Functions

• AVG

  Use the AVG function to return the average of the values in a specified field or expression. A specified field may be a calculated field.

  AVG([ALL] <field_1> | DISTINCT <field_2>)

  Use AVG to calculate the average value for a numeric field. As an aggregate function, AVG performs its calculation aggregating values in the same field or fields across all rows in a dataset.

  Field values of zero are included in the averaging, for instance, values of 1, 2, 3, 0, 0, and 0 result in an average of 1. NULL column values are not included in the calculation.

  SELECT AVG(AU_GPT)

  FROM G_ASSAY1

  WHERE AU_GPT > 0.1

  An example using an expression:

  SELECT AVG(AU_GPT * 0.25) AS AU_LOW_LIMIT

  FROM G_assays

  ALL returns the average for all rows. Unless DISTINCT is specified, ALL is the default behaviour.

  When averaging values in a specified field, DISTINCT ignores duplicate values.

  When using a GROUP BY clause, the AVG value is an aggregation of the specified field for all rows in each group.

• COUNT

  Use COUNT to returns the number of records that satisfy a query search condition.

  COUNT(* | [ALL] <field_name> | DISTINCT <field_name>)

  For example:

  SELECT COUNT (*) FROM G_HEADER WHERE XCOORD IS NULL

  In this case, the result set will contain only one record, with the record count as the only field value.

  ALL returns the count for all rows. Unless DISTINCT is specified, ALL is the default behaviour.

  When counting rows, DISTINCT ignores duplicate values in the specified field.

• MIN and MAX

  Use MIN and MAX functions to calculate the largest/smallest value for a numeric field. MIN and MAX work by aggregating values in the same field(s) across all records in a dataset. The dataset may be the entire table, a filtered dataset, or a logical group produced by a GROUP BY clause.

  Field values of zero are included in the aggregation. NULL column values are not included in the calculation. If the number of qualifying rows is zero, MIN and MAX return a NULL value.

  MAX([ALL] <field_name> | DISTINCT <field_name>)

  MIN([ALL] <field_name> | DISTINCT <field_name>)

  For example:

  SELECT MIN(XCOORD), MAX(XCOORD), MIN(YCOORD), MAX(YCOORD)

  FROM G_HEADER

• SUM

  Use SUM to sum all the values in a specified field. SUM works by aggregating values in the same field(s) across all records in a dataset. The dataset may be the entire table, a filtered dataset, or a logical group produced by a GROUP BY clause. Field values of zero are included in the aggregation. NULL column values are not included in the calculation. If the number of qualifying rows is zero, SUM returns a NULL value.

  SUM([ALL] <field_name> | DISTINCT <field_name>)

  For example:

  SELECT BHID, SUM(AU) AS HOLE_AU

  FROM G_ASSAY

  GROUP BY BHID

  When used with a GROUP BY clause, as above, SUM returns values which are an aggregation of the specified field for all records in each group.

Predicates