← SQL roadmap

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

FunctionWhat it doesExample → result
COUNT(*)Counts all rowsCOUNT(*) → 5
COUNT(col)Counts non-NULL valuesCOUNT(phone) → 3
COUNT(DISTINCT col)Counts unique non-NULL valuesCOUNT(DISTINCT city) → 2
SUM(col) / SUM(DISTINCT col)TotalSUM(amount) → 4500
AVG(col)Mean of non-NULL valuesAVG(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 groupGROUP_CONCAT(name ORDER BY name SEPARATOR ', ') → 'Asha, Ravi'
STD() / STDDEV_POP() / STDDEV_SAMP()Standard deviationSTDDEV_SAMP(marks)
VAR_POP() / VAR_SAMP()VarianceVAR_POP(marks)
BIT_AND / BIT_OR / BIT_XORBitwise aggregateBIT_OR(flags)
JSON_ARRAYAGG(col)Group into a JSON arrayJSON_ARRAYAGG(name) → '["Asha", "Ravi"]'
JSON_OBJECTAGG(k, v)Group into a JSON objectJSON_OBJECTAGG(sku, qty) → '{"A1": 5}'
SUM(CASE WHEN ... THEN 1 END)Conditional countSUM(CASE WHEN placed THEN 1 ELSE 0 END)
GROUP BY ... WITH ROLLUPAdds subtotal and grand-total rowsGROUP BY dept WITH ROLLUP
GROUPING(col)1 on ROLLUP super-aggregate rowsIF(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()).

Practise SQL with the AI mentor

Lesson test

Take an 8-question test on “Functions Reference: Aggregate Functions Deep Dive” — concepts, output prediction, bug spotting and interview questions.

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.