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 |
ALL |
Includes all rows where expression is not |
AVG( [ DISTINCT | ALL ] expression)
-
Average (arithmetic mean) of all input
expression
values COUNT(*)
-
Number of input rows, including rows that are
NULL
COUNT( [ DISTINCT | ALL ] expression )
-
Number of input rows where expression value is not equal to
NULL
MAX( [ DISTINCT | ALL ] expression)
-
Maximum value of expression across all input values
MIN( [ DISTINCT | ALL ] expression)
-
Minimum value of expression across all input values
SUM( [ DISTINCT | ALL ] expression)
-
Sum of
expression
across all input values LISTAGG( [DISTINCT | ALL ] expression,'delimiter')WITHIN GROUP (ORDER BY order_list [ASC | DESC++]++)
-
Displays the order of data within each group specified in the ORDER BY clause and then concatenates the values of the measure column.
-
As a single-set aggregate function, LISTAGG operates on all rows and returns a single output row.
-
As a group-set aggregate, the function operates on and returns an output row for each group defined by the GROUP BY clause.
-
As an analytic function, LISTAGG partitions the query result set into groups based on one or more expression in the query_partition_clause.
-
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.5
SELECT AVG(a+b) FROM one;
AVG
----
15
SELECT AVG(a+b+c) FROM one;
AVG
----
27.5
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
LISTAGG()
SELECT LISTAGG(name, '; ') "Player List",
MIN(number) "Min Jersey #"
FROM hockey
WHERE position = 'Forward' and number < 20;
Player List Min Jersey #
---------------------------------------------------------- -------------
GREGORY CAMPBELL; NATHAN HORTON; MILAN LUCIC; TYLER SEGUIN 11
SELECT LISTAGG(name, '; ')
WITHIN GROUP (ORDER BY number, name asc) "Player List",
MIN(number) "Min Jersey #"
FROM hockey
WHERE position = 'Forward' and number < 20;
Player List Min Jersey #
---------------------------------------------------------- -------------
GREGORY CAMPBELL; MILAN LUCIC; NATHAN HORTON; TYLER SEGUIN 11