Topic 38 of 52 · Intermediate
Functions Reference: Aggregate Functions Deep Dive
COUNT variants, conditional aggregation, GROUP_CONCAT options, ROLLUP and statistics.
Concept
Aggregate functions reduce many rows to one value per group. Without GROUP BY the whole table is one group.
Aggregates ignore NULLs, except COUNT(*), which counts rows. AVG(col) divides by the number of non-NULL values.
Conditional aggregation — SUM(CASE WHEN ... THEN 1 ELSE 0 END) — pivots rows into columns in a single pass.
Cheat sheet
| Function | What it does | Example → result |
|---|---|---|
| COUNT(*) | Counts all rows | COUNT(*) → 5 |
| COUNT(col) | Counts non-NULL values | COUNT(phone) → 3 |
| COUNT(DISTINCT col) | Counts unique non-NULL values | COUNT(DISTINCT city) → 2 |
| SUM(col) / SUM(DISTINCT col) | Total | SUM(amount) → 4500 |
| AVG(col) | Mean of non-NULL values | AVG(marks) → 71.2 |
| MIN(col) / MAX(col) | Smallest / largest (numbers, text, dates) | MAX(created_at) → latest date |
| GROUP_CONCAT(col ORDER BY ... SEPARATOR ...) | Joins values of a group | GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') → 'Asha, Ravi' |
| STD() / STDDEV_POP() / STDDEV_SAMP() | Standard deviation | STDDEV_SAMP(marks) |
| VAR_POP() / VAR_SAMP() | Variance | VAR_POP(marks) |
| BIT_AND / BIT_OR / BIT_XOR | Bitwise aggregate | BIT_OR(flags) |
| JSON_ARRAYAGG(col) | Group into a JSON array | JSON_ARRAYAGG(name) → '["Asha", "Ravi"]' |
| JSON_OBJECTAGG(k, v) | Group into a JSON object | JSON_OBJECTAGG(sku, qty) → '{"A1": 5}' |
| SUM(CASE WHEN ... THEN 1 END) | Conditional count | SUM(CASE WHEN placed THEN 1 ELSE 0 END) |
| GROUP BY ... WITH ROLLUP | Adds subtotal and grand-total rows | GROUP BY dept WITH ROLLUP |
| GROUPING(col) | 1 on ROLLUP super-aggregate rows | IF(GROUPING(dept), 'TOTAL', dept) |
| ANY_VALUE(col) | Any value from the group (ONLY_FULL_GROUP_BY safe) | ANY_VALUE(city) |
Code (SQL · MySQL 8)
SELECT IF(GROUPING(d.name), 'ALL BRANCHES', d.name) AS branch,
COUNT(*) AS students,
COUNT(s.offer_ctc) AS placed,
SUM(CASE WHEN s.offer_ctc >= 10 THEN 1 ELSE 0 END) AS dream_offers,
ROUND(AVG(s.offer_ctc), 2) AS avg_ctc_lpa,
MAX(s.offer_ctc) AS highest_ctc,
GROUP_CONCAT(DISTINCT s.company ORDER BY s.company SEPARATOR ', ') AS recruiters
FROM students s
JOIN dept d ON d.id = s.dept_id
GROUP BY d.name WITH ROLLUP;Remember
- COUNT(*) counts rows; COUNT(col) skips NULLs.
- Filter rows with WHERE before aggregating, filter groups with HAVING after.
- GROUP_CONCAT is truncated at group_concat_max_len (1024 by default).
- Every non-aggregated SELECT column must appear in GROUP BY (ONLY_FULL_GROUP_BY).
Interview questions
COUNT(*) vs COUNT(1) vs COUNT(col)?
COUNT(*) and COUNT(1) both count all rows and perform the same; COUNT(col) counts only rows where col is not NULL.
How do you pivot rows to columns without PIVOT?
Use conditional aggregation: SUM(CASE WHEN month = 'Jan' THEN amount END) AS jan, ... grouped by the row key.
What does WITH ROLLUP add?
Extra rows with subtotals for each grouping level and a grand total, where the rolled-up column is NULL (detect with GROUPING()).
Lesson test
Take an 8-question test on “Functions Reference: Aggregate Functions Deep Dive” — concepts, output prediction, bug spotting and interview questions.