1 Reply by GaryP

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

Re: SQL to 'group' selection

I am trying to get a list of taxon occurences grouped by record type for a sample reference. This is to give me a list sorted in ascending/descending order. For example for record type 'Field record'. I want to create a list that shows record type 'Field record', species name, then a count of the number found.

I think yiu can use the group command? If anyone has any thoughts otherwise I will go and experiment!

Thanks

Gary

2 Reply by MikeWeideli (edited by MikeWeideli 28-06-2011 09:39:03)

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

Re: SQL to 'group' selection

Gary

I think this is a straightforward case of Group By. The basic construct would be as follows

SELECT COUNT(TAXON_OCCURRENCE.TAXON_OCCURRENCE_KEY), RECORD_TYPE_KEY,TAXON_LIST_ITEM_KEY  FROM
TAXON_OCCURRENCE INNER JOIN TAXON_DETERMINATION ON
TAXON_DETERMINATION.TAXON_OCCURRENCE_KEY = TAXON_OCCURRENCE.TAXON_OCCURRENCE_KEY AND TAXON_DETERMINATION.PREFERRED = 1
GROUP BY RECORD_TYPE_Key,TAXON_LIST_ITEM_KEY

Mike Weideli

MikeWeideli's Website

3 Reply by GaryP

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

Re: SQL to 'group' selection

Thanks for your reply Mike. I was thinking along those lines but not sure of the format.

Cheers for now

Gary Pocklington