Aggregates, GROUP BY & HAVING

Aggregation changes the grain of a result. Instead of one row per source record, you can produce one row for the whole input or one row per group.

Core aggregate functions

COUNT counts rows or non-NULL expressions; SUM and AVG combine numeric values; MIN and MAX return extrema.

SELECT COUNT(*) AS order_count,
       SUM(total) AS total_value,
       AVG(total) AS average_value,
       MIN(total) AS smallest_order,
       MAX(total) AS largest_order
FROM orders;

Run the query to see the result.

COUNT(*) includes every row. COUNT(column) ignores rows where that column is NULL.

GROUP BY defines output grain

SELECT status,
       COUNT(*) AS orders,
       SUM(total) AS total_value
FROM orders
GROUP BY status
ORDER BY status;

Run the query to see the result.

Every selected non-aggregate expression should be functionally compatible with the grouping. Some engines are stricter than others; write SQL that makes the grouping intention explicit.

WHERE versus HAVING

WHERE filters individual rows before aggregation. HAVING filters groups after aggregation.

SELECT customer_id,
       COUNT(*) AS order_count,
       SUM(total) AS customer_total
FROM orders
WHERE status <> 'cancelled'
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY customer_total DESC;

Run the query to see the result.

Use WHERE whenever the rule applies to source rows; use HAVING when the rule depends on an aggregate result.

Conditional aggregation

CASE inside an aggregate can produce multiple metrics in one pass.

SELECT customer_id,
       COUNT(*) AS all_orders,
       SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_orders,
       SUM(CASE WHEN status = 'paid' THEN total ELSE 0 END) AS paid_total
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

Run the query to see the result.

Grouping across joins

When joining one-to-many tables before aggregating, be aware that the join changes row count. A customer joined to three orders appears three times before grouping. This is expected, but joining multiple one-to-many relationships at once can multiply rows and inflate sums.

Result-grain checklist

Before writing an aggregate query, state the desired grain in one sentence: “one row per customer,” “one row per build,” or “one row per day and status.” Then make GROUP BY match that sentence.

Practice: produce one row per test_results.build_id with total tests, passed tests, failed tests, and errors.