Aggregation functions

You'll be presented to the SQL functions from SlicingDice that can perform data aggregations.


COUNT

Returns the number of rows matching the query criteria.

Requires:

  • DISTINCT (optional): Keyword that will enforce that only distinct, non-null field values will be counted
  • column_name | *: The column name to count or the * for count for all columns on the specified dimension.
Syntax: COUNT([DISTINCT]column_name | *)
SELECT COUNT(*) FROM account WHERE industry = 'retail'
            --Result: 50
            SELECT COUNT(DISTINCT[country] FROM account WHERE industry = 'retail';
            --Result: 10

AVG

Returns the average of the column values.

Requires:

  • column_name | *: The column name to average or the * for average all columns on the specified dimension.

Syntax:  AVG(column_name | *)

SELECT companyname, AVG(AnnualRevenue) FROM account WHERE industry = 'retail'  GROUP BY companyname

MIN

Returns the minimum column value.

Requires:

  • column_name | *: The column name to select the minimum value or the * for select the minimum value based on all columns on the specified dimension.
Syntax:  MIN(column_name | *)
SELECT MIN(AnnualRevenue), companyname FROM account WHERE industry = 'retail' GROUP BY companyname

MAX

Returns the maximum column value.

Requires:

  • column_name | *: The column name to select the maximum value or the * for select the maximum value based on all columns on the specified dimension.
Syntax:  MAX(column_name | *)
SELECT companyname, MAX(AnnualRevenue) FROM account WHERE industry = 'retail' GROUP BY companyname

SUM

Returns the total sum of the column values.

Requires:

  • column_name | *: The column name to sum values or the * for sum all values based on all columns on the specified dimension.
Syntax:  SUM(column_name | *)
SELECT SUM(AnnualRevenue) FROM account WHERE industry = 'retail'