Aggregates and GROUP BY

Summarising Data

Aggregate functions collapse many rows into one summary value.

SELECT COUNT(*) FROM customers;
SELECT MIN(total), MAX(total), AVG(total), SUM(total) FROM orders;

Common Aggregate Functions

• COUNT(*) counts all rows; COUNT(col) counts non-null values.
• SUM(col) adds numeric values.
• AVG(col) calculates the mean.
• MIN and MAX find extremes.

GROUP BY

SELECT city, COUNT(*) AS total_customers
FROM customers
GROUP BY city;

SELECT customer_id, SUM(total) AS lifetime
FROM orders
GROUP BY customer_id
ORDER BY lifetime DESC;

GROUP BY splits rows into groups; the aggregate runs once per group.

Filtering Groups With HAVING

SELECT customer_id, SUM(total) AS lifetime
FROM orders
GROUP BY customer_id
HAVING SUM(total) > 500;

WHERE filters rows before grouping; HAVING filters the groups afterwards.

Key Points

• Aggregates summarise many rows into one value.
• GROUP BY creates the groups the aggregate works on.
• HAVING filters groups; WHERE filters rows.
• Every unaggregated column must appear in GROUP BY.