Aggregate Functions and Grouping

Everything so far returned rows from the table. Reports almost never want rows. They want totals, counts and averages: how many orders, how much revenue, which category sells most.

Aggregate functions collapse many rows into one value. GROUP BY decides how many collapses happen and over which rows.

The five core aggregates#

SELECT COUNT(*)            AS employees,
       SUM(salary)         AS payroll,
       ROUND(AVG(salary),2) AS avg_salary,
       MIN(salary)         AS lowest,
       MAX(salary)         AS highest
FROM employees;

 employees | payroll | avg_salary | lowest | highest
-----------+---------+------------+--------+---------
        10 | 1160000 | 116000.00  |  70000 |  185000

With no GROUP BY, the whole table is one group and you get exactly one row back, always, even if the table is empty.

How NULL behaves#

Aggregates skip NULL values, with one exception.

SELECT COUNT(*) AS all_rows, COUNT(phone) AS with_phone FROM customers;

 all_rows | with_phone
----------+------------
       10 |          7

COUNT(*) counts rows. COUNT(column) counts rows where that column is not NULL. COUNT(DISTINCT column) counts the unique non-null values. Mixing these up is the most common reporting error there is.

AVG ignores NULLs too, which changes the denominator. The average of 10, 20 and NULL is 15, not 10. If a missing value should count as zero, say so with COALESCE:

SELECT AVG(COALESCE(bonus, 0)) FROM staff;

On an empty set, COUNT returns 0 but SUM, AVG, MIN and MAX return NULL. A dashboard that does arithmetic on an empty-period SUM will propagate that NULL everywhere unless you wrap it.

GROUP BY#

GROUP BY splits the rows into buckets by the listed columns and runs the aggregates once per bucket.

SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
ORDER BY orders DESC, customer_id;

 customer_id | orders
-------------+--------
           1 |      3
           2 |      2
           3 |      2
           6 |      2
           4 |      1
           5 |      1
           7 |      1
           8 |      1
           9 |      1
(9 rows)

Nine rows, not ten. Customer 10 has no orders, so no row in orders carries that ID and no group exists for it. Joins fix that in the next lesson.

 orders (14 rows)                 groups after GROUP BY customer_id
 +----------+-------------+      +-------------+---------+
 | order_id | customer_id |      | customer_id | COUNT   |
 |    1     |      1      | ---> |      1      |    3    |
 |    2     |      1      | ---> |             |         |
 |   10     |      1      | ---> |             |         |
 |    3     |      2      | ---> |      2      |    2    |
 |    7     |      2      | ---> |             |         |
 |   ...    |     ...     |      |     ...     |   ...   |

The rule that trips everyone#

Every column in the SELECT list must either appear in GROUP BY or sit inside an aggregate. The database cannot show one full_name for a bucket that holds three different names.

SELECT customer_id, order_date, COUNT(*) FROM orders GROUP BY customer_id;
-- ERROR: column "orders.order_date" must appear in the GROUP BY clause
--        or be used in an aggregate function

Add the column to GROUP BY if you want a bucket per distinct pair, or aggregate it (MAX(order_date)) if you want one value per bucket. MySQL's older default would silently pick an arbitrary value instead of failing. PostgreSQL's strictness is a favor.

You can group by an expression, and by output position:

SELECT CASE WHEN unit_price >= 300 THEN 'premium'
            WHEN unit_price >= 50  THEN 'mid'
            ELSE 'budget' END AS tier,
       COUNT(*) AS products
FROM products
GROUP BY 1
ORDER BY 2 DESC;

  tier   | products
---------+----------
 budget  |        5
 mid     |        4
 premium |        2

WHERE versus HAVING#

WHERE filters rows before grouping. HAVING filters groups after the aggregates are computed. Use WHERE for anything that can be decided per row, because it shrinks the work before grouping starts.

Revenue per category, ignoring cancelled orders, keeping only categories above 500:

SELECT p.category,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders   o ON o.order_id   = oi.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.status <> 'cancelled'                     -- row-level: before grouping
GROUP BY p.category
HAVING SUM(oi.quantity * oi.unit_price) > 500     -- group-level: after aggregating
ORDER BY revenue DESC;

  category   | revenue
-------------+---------
 Displays    |  867.00
 Furniture   |  848.00
 Accessories |  823.50
(3 rows)

Without the HAVING, three more categories appear: Audio at 398.00, Office at 144.00, Video at 59.00. The status filter could never go in HAVING, because status is not a grouped column.

A good test: if the condition mentions an aggregate, it belongs in HAVING. If it mentions a plain column, it belongs in WHERE.

Conditional aggregation with FILTER#

Often you want several counts side by side, each with its own condition. PostgreSQL has FILTER for exactly this:

SELECT customer_id,
       COUNT(*)                                          AS total,
       COUNT(*) FILTER (WHERE status = 'delivered')      AS delivered,
       COUNT(*) FILTER (WHERE status IN ('pending','paid')) AS open_orders
FROM orders
GROUP BY customer_id
ORDER BY customer_id
LIMIT 4;

 customer_id | total | delivered | open_orders
-------------+-------+-----------+-------------
           1 |     3 |         2 |           0
           2 |     2 |         2 |           0
           3 |     2 |         1 |           0
           4 |     1 |         0 |           0

The portable version, which works in MySQL and SQL Server too, uses CASE:

SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered

This turns rows into columns. It is the standard way to build a pivot in SQL.

What happens under the hood#

The database groups rows with one of two strategies. It can sort the input by the grouping columns and scan once, closing each group as the key changes. Or it can build a hash table keyed by the grouping columns and update a running total per key.

Hash aggregation is typically faster when the number of distinct groups fits in memory. Sorting wins when the data is already ordered, such as when an index supplies that order. You do not choose. The planner does, and EXPLAIN shows which it picked as HashAggregate or GroupAggregate.

Common mistakes#

Counting after a fan-out join. Joining orders to order_items produces one row per line, so COUNT(*) counts lines, not orders. The course data shows it: 14 orders but 26 joined rows. Use COUNT(DISTINCT order_id).

Putting a row condition in HAVING. It works in some databases and is slower everywhere, because the rows are grouped first and filtered after.

Selecting a column that is not grouped. PostgreSQL rejects it. Resist the urge to "fix" it by grouping by every column, which gives you back the raw rows.

Forgetting that AVG skips NULL. Decide whether missing means zero or unknown.

Comparing floats from AVG. Averages of integers return NUMERIC. Round for display, never for comparison.

Practice#

1. Count orders per status. You should see delivered 8, pending 2, shipped 2, cancelled 1, paid 1.
2. For each department id in employees, show headcount and average salary, keeping only departments with at least 2 employees. Which department drops out?
3. Calculate total revenue per month for non-cancelled orders (use TO_CHAR(order_date, 'YYYY-MM')). February should total 439.00.
4. Write one query that returns, per customer, total orders and cancelled orders using FILTER. Then rewrite it with CASE.
5. Explain why SELECT COUNT(*), customer_id FROM orders; fails, and write two valid fixes with different meanings.