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 | 
 +-------------+----------------+-------+------*/