Aggregate Functions

Example 1 - COUNT (All records)

Count returns the number on none null records. Returns a count of all records in the table.

SELECT COUNT (*) FROM Taxon_Occurrence

Example 2  - COUNT AND GROUP BY

Returns a count of each of the Determination types. The GROUP BY clause must contain the name of the fields included in the grouping apart from the field actual being aggregated. In XML reports it is always necessary to specify a name for the grouped field. (eg COUNTOFRECORDS).

 Example 4 - HAVING

The following introduces the HAVING clause which works on the calculated result.

SELECT DETERMINATION_TYPE.SHORT_NAME,COUNT (Taxon_Occurrence.Taxon_Occurrence_Key)  AS RECORDS FROM Taxon_Occurrence INNER JOIN Taxon_Determination ON Taxon_Determination.Taxon_Occurrence_Key = Taxon_Occurrence.Taxon_Occurrence_Key INNER JOIN Determination_Type ON Determination_Type.Determination_Type_Key = Taxon_Determination.Determination_Type_Key GROUP BY DETERMINATION_TYPE.SHORT_NAME 
HAVING COUNT (Taxon_Occurrence.Taxon_Occurrence_Key) > 10

Example 4 - MAX/MIN

MAX returns the maximum value in the expression and MIN the minimum value. The following returns the maximum in the Data field of Taxon_Occurrence_Data, but ignores non numeric values. 

SELECT MAX(Taxon_Occurrence_Data.Data) FROM Taxon_Occurrence_Data  where ISNUMERIC(Taxon_Occurrence_Data.Data)=1;

The ISNUMERIC functions takes a broad  view of what a number is, so if you get unexpected results there is a better function which could be added to Recorder. (dbo.LCisReallyNumeric)

Special consideration is required if trying to determine the earliest and latest date for a group of record where VagueDates are involved.