Aggregate functions collapse many rows into one value: how many, how much, the average, the extremes.

Function	Returns	NULLs
`COUNT(*)`	Number of rows	Counted
`COUNT(col)`	Number of non-null values	Ignored
`COUNT(DISTINCT col)`	Number of different non-null values	Ignored
`SUM(col)`	Total	Ignored (all `NULL` → `NULL`)
`AVG(col)`	Mean = `SUM / COUNT(col)`	Ignored
`MIN(col)` / `MAX(col)`	Smallest / largest (also for text and dates)	Ignored

SQL

SELECT COUNT(*)                 AS employees,
       COUNT(email)             AS with_email,
       COUNT(DISTINCT dept_id)  AS departments,
       SUM(salary)              AS payroll,
       AVG(salary)              AS avg_salary,
       MIN(hire_date)           AS first_hire,
       MAX(name)                AS last_name_alpha
FROM employees;

Output

employees | with_email | departments | payroll | avg_salary | first_hire | last_name_alpha
----------+------------+-------------+---------+------------+------------+----------------
10        | 8          | 3           | 937000  | 93700.0    | 2015-03-01 | Vikram

NULLs change averages

SQL

CREATE TABLE ratings (product TEXT, stars INTEGER);
INSERT INTO ratings VALUES ('A', 5), ('A', 3), ('A', NULL), ('B', NULL);

SELECT product,
       COUNT(*)              AS rows_,
       COUNT(stars)          AS rated,
       AVG(stars)            AS avg_ignoring_null,
       AVG(COALESCE(stars, 0)) AS avg_null_as_zero,
       SUM(stars)            AS total
FROM ratings
GROUP BY product
ORDER BY product;

Output

product | rows_ | rated | avg_ignoring_null | avg_null_as_zero   | total
--------+-------+-------+-------------------+--------------------+------
A       | 3     | 2     | 4.0               | 2.6666666666666665 | 8
B       | 1     | 0     | NULL              | 0.0                | NULL

Decide what NULL means for your metric. Product B shows the other surprise: SUM over only NULLs is NULL, not 0 — wrap it: COALESCE(SUM(x), 0).

Aggregates over an empty set

SQL

SELECT COUNT(*) AS n, SUM(salary) AS total, MAX(salary) AS top
FROM employees
WHERE dept_id = 4;             -- Marketing has nobody

Output

n | total | top
--+-------+-----
0 | NULL  | NULL

Conditional aggregation

SQL

SELECT SUM(CASE WHEN category = 'Electronics' THEN price END) AS electronics_value,
       AVG(CASE WHEN category = 'Furniture'   THEN price END) AS avg_furniture,
       COUNT(CASE WHEN price < 100 THEN 1 END)                 AS cheap_items
FROM products;

Output

electronics_value | avg_furniture | cheap_items
------------------+---------------+------------
69500             | 6250.0        | 2

What is the difference between COUNT(*), COUNT(1) and COUNT(column)?

Does AVG include NULL values?