Aggregate Functions

Many rows in, one row out#

An aggregate function takes a set of rows and collapses them into a single value.

SELECT COUNT(*)        AS products,
       SUM(stock)      AS units,
       AVG(price)      AS avg_price,
       MIN(price)      AS cheapest,
       MAX(price)      AS priciest
FROM products;

Without GROUP BY, the whole table is one group, so you get exactly one output row.

COUNT has three faces#

SELECT COUNT(*)           FROM customers;  -- 4, counts rows
SELECT COUNT(city)        FROM customers;  -- 3, skips NULL
SELECT COUNT(DISTINCT city) FROM customers; -- 2, unique non-NULL

COUNT(*) counts rows. COUNT(column) counts rows where that column is not NULL. That difference is behind a huge number of wrong reports.

NULL handling#

SUM, AVG, MIN, and MAX all ignore NULLs. That matters for AVG: if three rows have values 10, 20, and NULL, the average is 15, not 10. If you want NULL treated as zero:

SELECT AVG(COALESCE(discount, 0)) FROM orders;

On an empty set, COUNT returns 0 but SUM returns NULL. Wrap it when you need a number: COALESCE(SUM(qty), 0).

GROUP_CONCAT#

SELECT customer_id, GROUP_CONCAT(status ORDER BY ordered_at SEPARATOR ', ') AS history
FROM orders
GROUP BY customer_id;

It builds a delimited string per group. The result is truncated at group_concat_max_len, which defaults to 1024 bytes. Silent truncation has fooled plenty of people, so raise it with SET SESSION group_concat_max_len = 100000; if the output is big.

What it looks like inside#

rows:   10  20  NULL  30
          \  |   x    /       NULL discarded
           SUM ▶ 60   AVG ▶ 20   COUNT(col) ▶ 3   COUNT(*) ▶ 4

Aggregates can't go in WHERE#

SELECT * FROM products WHERE price > AVG(price);   -- error

WHERE runs before aggregation, so the average doesn't exist yet. Use a subquery (Module 5) or HAVING after grouping.

Common mistakes#

• Using COUNT(column) and expecting it to count all rows.
• Forgetting that AVG skips NULLs and so skews low or high depending on intent.
• Trusting GROUP_CONCAT output without checking its length limit.
• Using SUM on an empty set and getting NULL into your report.

Practice#

Compute the total number of order items, the total quantity sold, and the average line total (qty * unit_price). Then count customers with and without a city using two different COUNT forms and explain why the numbers differ. Finally, produce one row per order with a comma-separated list of product ids in it.