Aggregate Functions
Compute summary statistics across groups of rows.
The core five
SQL — full stats on the orders table
SELECT
COUNT(*) AS total_orders,
COUNT(amount) AS orders_with_amount, -- excludes NULLs
SUM(amount) AS total_revenue,
AVG(amount) AS avg_order_value,
MIN(amount) AS smallest_order,
MAX(amount) AS largest_order
FROM orders;
COUNT variations
SQL
SELECT
COUNT(*) -- all rows including NULLs
COUNT(phone) -- only rows where phone is NOT NULL
COUNT(DISTINCT city) -- number of unique cities
COUNT(DISTINCT country) -- number of unique countries
FROM customers;
SUM with CASE — conditional aggregation
SQL — count orders by status in one query
SELECT
SUM(CASE WHEN amount > 500 THEN 1 ELSE 0 END) AS large_orders,
SUM(CASE WHEN amount <= 500 THEN 1 ELSE 0 END) AS small_orders
FROM orders;
AVG — watch out for NULLs and integer division
SQL
-- AVG ignores NULLs — this can skew your results
SELECT AVG(amount) FROM orders;
-- To include NULLs as 0:
SELECT AVG(COALESCE(amount, 0)) FROM orders;
-- Round the result:
SELECT ROUND(AVG(amount), 2) FROM orders;
MIN and MAX on non-numeric columns
SQL
SELECT
MIN(name) AS first_alphabetically,
MAX(name) AS last_alphabetically
FROM customers;
FILTER clause (PostgreSQL, SQLite)
SQL — conditional aggregation, cleaner syntax
SELECT
COUNT(*) FILTER (WHERE amount > 500) AS large_orders,
COUNT(*) FILTER (WHERE amount <= 500) AS small_orders,
SUM(amount) FILTER (WHERE product = 'Laptop') AS laptop_revenue
FROM orders;
Key rules to remember
| Rule | Detail |
|---|---|
| NULLs | All aggregate functions (except COUNT(*)) ignore NULLs |
| Empty set | COUNT returns 0; SUM/AVG/MIN/MAX return NULL on an empty set |
| DISTINCT | Any aggregate can use DISTINCT: SUM(DISTINCT amount) |
| SELECT rule | Non-aggregate columns in SELECT must appear in GROUP BY |
Next step
Aggregate functions combine powerfully with window functions (OVER clause) which compute running totals, ranks, and moving averages without collapsing rows. Check out the SQL Practice section for hands-on window function problems.