SQL Aggregate Functions

General form

To execute an aggregate function and pass all values from a column to it, use the following syntax:

SELECT AGG_FUNCTION(column_name) 
FROM table_name;

The function will take all values from the specified column and produce a single cell. Consequently, running n aggregate functions simultaneously produces n cells.

Vendors of different SQL-compatible database management systems provide their users with slightly different aggregate functions. Here is a list of aggregate functions according to the ANSI SQL standard:

• MIN
• MAX
• AVG
• COUNT
• SUM

There are also a few more elaborate functions related to statistics, such as STDDEV_POP for the population standard deviation or CORR for the correlation coefficient. It may be worth your while to check out the complete list of aggregate functions.

Example

Consider this table named stocks:

Using the MAX function to find the highest price among all the stocks:

SELECT MAX(price) 
FROM stocks;

This query will produce 89.8. Likewise, the MIN function for the same column would return 15.6. To find out the count of deals made yesterday, use this query:

SELECT SUM(yesterday_deals) 
FROM stocks;

It will return 65.

Numeric types like INT or REAL naturally fit most aggregate functions. However, it also makes sense to use aggregate functions with other data types in some cases. For example, the MIN and MAX functions can be used to find the lexicographically smallest and largest strings.

Adding WHERE statement

It is also possible to use WHERE to choose a subset of rows on which we want to run our aggregation functions. For example, to find the average price and average count of deals for all stocks that cost more than 40, the query will be:

SELECT 
    AVG(price) AS avg_price,
    AVG(yesterday_deals) AS avg_deals 
FROM 
    stocks 
WHERE 
    price > 40;

The answer will be:

DISTINCT keyword

To omit all duplicate values, place the DISTINCT keyword inside the brackets of your aggregate function:

SELECT COUNT(DISTINCT yesterday_deals) 
FROM stocks;

This query will return 4 as there are only four distinct numeric values in the column yesterday_deals.

Determining the set of unique values can be costly in terms of computation complexity, so don't overuse DISTINCT.

COUNT(*)

A regular call of the COUNT function with a column name as an argument will count the total amount of values in the column. If you call COUNT with an asterisk, you're telling the function to count all rows in the table. The final result won't be affected by the particular types of columns or the values that their cells store. For our stocks table, COUNT(*) will return 7.

All aggregate functions except COUNT(*) ignore the NULL values.