Aggregate Functions: avg(), max(), min(), sum(), count(), first(), last()

📖 Theory

One number from a thousand rows

Meera does not want to see every sale, she wants the total. Or the average price. Or how many items she stocks. One number that summarises the whole table.

That is an aggregate function: it takes many rows and returns one answer. And you already met these in Excel, SUM, AVERAGE, COUNT, MAX, MIN, now in SQL, and they gain a superpower: paired with GROUP BY, they answer 'per category' instead of 'for everything'.

This is where a database out-muscles a calculator: one line summarises a million rows.

📖 Theory

A juicer for data

A juicer takes a heap of oranges and produces one glass of juice, many things in, one thing out. Aggregate functions are a juicer for rows: pour in 500 sale rows, get out one total, or one average, or one count. And GROUP BY is a juicer with separate jugs: one glass of grocery-juice, one of dairy-juice, one summary per group instead of one for the whole pile.

⌨️ Practical

Summarise the sales table

-- Whole-table aggregates (one answer each)
SELECT SUM(qty)   AS total_qty,   -- 20+15+30+25+40 = 130
       AVG(price) AS avg_price,   -- (45+120+60+35+30)/5 = 58
       COUNT(*)   AS num_items,   -- 5
       MAX(price) AS costliest,   -- 120
       MIN(price) AS cheapest     -- 30
FROM sales;

-- Per-category with GROUP BY
SELECT category, SUM(qty) AS qty_sold
FROM sales
GROUP BY category;
-- Grocery 50, Beverage 15, Personal 25, Dairy 40

This example runs in Gri-Learn on the web, where you can edit it and see the output.

📖 Theory

GROUP BY and HAVING

Alone, an aggregate summarises the whole table. Add GROUP BY and it summarises each group:

SELECT category, SUM(qty) FROM sales GROUP BY category gives total quantity per category.

And to filter the groups? WHERE cannot, it filters rows before grouping. You need HAVING, which filters after aggregation:

... GROUP BY category HAVING SUM(qty) > 30

keeps only categories whose total exceeds 30. WHERE filters rows; HAVING filters groups. That pairing is a classic exam point.

🧩 Quiz

A column has 5 rows: 3 hold values, 2 are NULL. What do COUNT(*) and COUNT(column) return?

1. COUNT(*) = 5, COUNT(column) = 3 (it skips NULLs)
2. Both return 5
3. Both return 3
4. COUNT(*) = 3, COUNT(column) = 5

💭 Think first

WHERE or HAVING?

Meera wants 'categories whose TOTAL quantity sold exceeds 30'. She tries WHERE SUM(qty) > 30 and gets an error. Why can't WHERE do this, and what should she use?

⚠️ Watch out

Where marks leak

*COUNT() counts all rows; COUNT(col) skips NULLs (the COUNTA parallel). Using WHERE to filter aggregates, that is HAVING's job (WHERE runs before grouping). Forgetting that an aggregate collapses rows into one value (so you cannot SELECT a plain column alongside an aggregate unless it is in GROUP BY). And note FIRST/LAST** have limited/non-standard support, do not lean on them. AVG, SUM, COUNT, MAX, MIN are the reliable five.

📖 Theory

You have done this before, twice

SUM, AVG, COUNT, MAX, MIN, these are the exact functions from Unit 2's Excel lesson, and GROUP BY is exactly Consolidate. You clicked them in a spreadsheet; you type them in SQL; the ideas are identical. That transfer is the whole point of building this subject spreadsheet-first. Two lessons left: sequences for auto-numbering, and views. Next: scalar functions that transform each value one at a time.

✅ Summary

Key takeaways

• Aggregate functions collapse many rows into one value: SUM, AVG, COUNT, MAX, MIN.
• COUNT(*) counts all rows; COUNT(column) counts only non-NULL values (like Excel COUNTA vs COUNT).
• GROUP BY produces one aggregate PER group (SUM(qty) per category).
• HAVING filters groups after aggregation; WHERE filters rows before it.
• FIRST/LAST have limited support; rely on the core five.
• Memory hook: a juicer (many rows in, one answer out); GROUP BY gives separate jugs.