Aggregates and GROUP BY

Last reviewed May 28, 2026 Content v20260528

Track mode

sql_sandbox

Means

SQL sandbox

Reading

~1 min

Level

intermediate

This lesson

This lesson teaches Aggregates and GROUP BY: the SQL patterns, schema habits, and query reasoning you need before advancing in MySQL.

Teams query Aggregates and GROUP BY on every MySQL codebase—skipping it leaves gaps in debugging and data reviews.

You will apply Aggregates and GROUP BY in contexts like: Web apps on shared hosting, ecommerce, and many startups’ first production DB.

Copy MySQL SQL into the mysql client, local MySQL/MariaDB, or DB Fiddle (MySQL dialect)—use DESCRIBE and EXPLAIN where lessons show them. The in-browser lab ships later; mysql client is the practice path now.

When you can explain the previous lesson's ideas without copying example queries verbatim.

GROUP BY with HAVING; MySQL strict mode requires non-aggregated SELECT columns to appear in GROUP BY.

Aggregation

SELECT status, COUNT(*) AS cnt, SUM(total) AS revenue
FROM orders
GROUP BY status
HAVING SUM(total) > 1000;

Practice: Run on practice.

ONLY_FULL_GROUP_BY

Invalid selects like SELECT status, customer_id, COUNT(*) without grouping customer_id fail in strict mode—fix query or use ANY_VALUE() deliberately.

Important interview questions and answers

Q: HAVING vs WHERE?
A: WHERE filters rows before group; HAVING filters groups after aggregation.
Q: COUNT(*) vs COUNT(col)?
A: COUNT(col) ignores NULLs in col.

Self-check

Revenue by status over 1000 total.
Why ONLY_FULL_GROUP_BY?

Pitfall: SELECT col, COUNT(*) without GROUP BY col fails in strict mode.

Interview prep

HAVING?: Filters groups after aggregation.
COUNT(*)?: Counts rows including NULLs in other columns.

Discussion

Past discussion is visible to everyone. Only logged-in users can post comments and replies.

Starter discussion topics

HAVING vs WHERE?
Strict GROUP BY?

No discussion yet. Be the first to ask a question.