1 Reply by GaryP

  • GaryP
  • Veteran
  • Offline
  • Registered: 12-07-2009
  • Posts: 65

Re: Summarising taxon occurences

Hello,

I currently have an XML report that I have created which basically uses the sample and taxon occurence tables to display information. I would now like to create a similar report but instead of printing the taxon occurence details I would like to sum the number of taxon occurences for each sample. I still want to reatin the detail from the sample records but instead of printing the taxon detail just show how many for each sample record. Any thoughts?

Thanks

Gary Pocklington

2 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,627

Re: Summarising taxon occurences

Gary

The following is the basic structure. Any of the sample fields you want have to be included in both  the SELECT and GROUP BY clauses.  In XML reports it is always necessary to specify the name for the grouped field (eg. Records). 

SELECT Count(TOCC.Taxon_Occurrence_Key) AS Records, S.Sample_Key,S.Spatial_ref  FROM Taxon_Occurrence TOCC
INNER JOIN Sample S on S.Sample_Key = TOCC.Sample_Key GROUP BY S.Sample_Key,S.Spatial_ref

Mike Weideli

MikeWeideli's Website

3 Reply by GaryP

  • GaryP
  • Veteran
  • Offline
  • Registered: 12-07-2009
  • Posts: 65

Re: Summarising taxon occurences

Thanks Mike, that's what I was looking for.

Cheers for now

Gary