SQL Aggregate Functions

Aggregate functions compute a single result from a set of input values. The built-in aggregate functions are listed below.

The aggregate functions that accept an expression parameter can be modified by the keywords DISTINCT or ALL. If neither is specified, the result is the same as if ALL were specified.

DISTINCT Modifies the expression to include only distinct values that are not NULL
ALL Includes all rows where expression is not NULL

Examples

# Except COUNT(DISTINCT(expression)) and COUNT(expression), all examples use this 
# sample table
CREATE TABLE one (a INT, b DOUBLE, c STRING);
INSERT INTO one VALUES (11,1,11), (12,2,12), (13,3,13), (14,4,14);
SELECT * FROM one;
 A  B  C 
 -- -- --
 11 1  11
 12 2  12
 13 3  13
 14 4  14

AVG()

SELECT AVG(a) FROM one;
 AVG 
 ----
  12 
SELECT AVG(a+b) FROM one;
 AVG 
 ----
  15 
SELECT AVG(a+b+c) FROM one;
 AVG 
 ----
 27.5  

COUNT(*)

SELECT COUNT(*) FROM one;
 COUNT 
 ------
   4  

COUNT(expression)

CREATE TABLE tst_table (column1 string);
INSERT INTO tst_table VALUES ('a'),('b'),('c'),(NULL);
SELECT COUNT(*) FROM tst_table;
 COUNT 
 ------
   4   
SELECT COUNT(column1) FROM tst_table;
 COUNT 
 ------
   3

COUNT( DISTINCT | ALL expression)

CREATE TABLE two (a INT, b DOUBLE, c STRING);
INSERT INTO two VALUES(21,1,21), (22,2,22), (21,1,21), (21,1,21), (21,1,21), (22,2,22),(21,null,null);
 
SELECT COUNT(*) FROM two;
 COUNT 
 ------
   7  
 
SELECT COUNT(ALL c) FROM two;
 COUNT 
 ------
   6 
 
SELECT COUNT(DISTINCT a ) FROM two;
 COUNT 
 ------
   2  
 
SELECT COUNT(DISTINCT(a+b+c)) FROM two;
 COUNT 
 ------
   2  
 

MAX()

SELECT MAX(a) FROM one;
 MAX 
 ----
  14 

MIN()

SELECT MIN(a) FROM one;
 MIN
 ----
 11

SUM()

SELECT SUM(a) FROM one;
 SUM
 ----
 50