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.