Aggregate function calls

An aggregate function summarizes the rows of a group into a single value.

Aggregate function call syntax

function_name(
  [ DISTINCT ]
  function_arguments
)

Description

Each aggregate function supports all or a subset of the aggregate function call syntax. You can use the following syntax to build an aggregate function:

  • DISTINCT: Each distinct value of expression is aggregated only once into the result.

    • If DISTINCT is also specified, then the sort key must be the same as expression.

Details

The clauses in an aggregate function call are applied in the following order:

  • DISTINCT

When used in conjunction with a GROUP BY clause, the groups summarized typically have at least one row. When the associated SELECT statement has no GROUP BY clause or when certain aggregate function modifiers filter rows from the group to be summarized, it's possible that the aggregate function needs to summarize an empty group.

Aggregate function examples

A simple aggregate function call for COUNT, MIN, and MAX looks like this:

SELECT
  COUNT(*) AS total_count,
  COUNT(fruit) AS non_null_count,
  MIN(fruit) AS min,
  MAX(fruit) AS max
FROM
  (
    SELECT NULL AS fruit
    UNION ALL
    SELECT 'apple' AS fruit
    UNION ALL
    SELECT 'pear' AS fruit
    UNION ALL
    SELECT 'orange' AS fruit
  )

/*-------------+----------------+-------+------+
 | total_count | non_null_count | min   | max  |
 +-------------+----------------+-------+------+
 | 4           | 3              | apple | pear |
 +-------------+----------------+-------+------*/