FANDOM


What kinds of aggregation can you do in SQL?

Official SQL? Aggregation function Explanation
sum Sum of the column values
count Number of records in the table with non-NULL value in specified column
count distinct Number of distinct non-null values in a column of a table, i.e.

Number of distinct records in a table, removing dupes using a specified column as the key, and ignoring records with NULL for that column

min Minimum value in a table column
max Maximum value in a table column
? first The first record in a table (more meaningful when an order is specified).
? last

The last record in a table (more meaningful when an order is specified).

? group_concat Concatenates all values in each group using some separator (',' by default). Can be limited to distinct values. Admits alternative sort order within each group.

also known as 'listagg' in some DBMSes.

?

stddev_samp

Standard deviation of all values of specified columns (using sample std. deviation calculate).
? std_dev_popn Standard deviation of all values of specified column (using population std. deviation calculate).
? var_samp Variance of elements in the specified column (using sample calculation)
? var_popn Variance of elements in the specified column (using population calculation)
? median Median of values in the column. TODO: Is there a distinct variant?
? corr Correlation between the values of two columns, calculated over the table

Notes:

  • Generally, these aggregate functions ignore NULLs.

TODO: Do some of these have special semantics w.r.t. NULLs?

  • Not all DBMSes support all aggregation functions.