SQL GROUP BY & HAVING Explained with Examples

Your principal walks in and asks: "How many students are there in each class?" Notice what she did not ask. She doesn't want 5,000 names — she wants maybe 40 rows, one per class, with a count. Every dashboard you've ever seen — orders per city on Swiggy, videos per channel on YouTube, average salary per department — is this same shape: many rows in, one row per group out. This lesson teaches you that shape.

GROUP BY — sorting rows into buckets

SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department;

Engineering | 4
Sales       | 4
Marketing   | 2

Picture it physically: SQL reads every row and drops it into a bucket based on its department. Then each bucket collapses into exactly one output row. The individual employees are gone from the result — only bucket-level summaries remain. That single sentence explains half the errors people hit with GROUP BY.

Group by two columns — GROUP BY department, city — and a bucket forms per unique combination: (Sales, Delhi) and (Sales, Bangalore) are different buckets. It never means "group by department AND separately by city" — that would be two different queries.

Aggregate functions — summarising a bucket

Once rows are in buckets, aggregate functions compute one value per bucket. The big five: COUNT (how many), SUM (total), AVG (average), MIN / MAX (extremes).

SELECT department,
       COUNT(*)              AS emp_count,
       SUM(salary)           AS total_salary,
       ROUND(AVG(salary), 2) AS avg_salary,
       MAX(salary)           AS top_salary
FROM employees
GROUP BY department
ORDER BY total_salary DESC;

Engineering | 4 | 350000 | 87500.00 | 95000
Sales       | 4 | 247000 | 61750.00 | 72000
Marketing   | 2 | 120000 | 60000.00 | 60000

Fun fact: aggregates work even without GROUP BY — then the whole table is one big bucket. SELECT COUNT(*) FROM employees; returns a single number.

The COUNT family — three cousins, three answers

On the same data, these can return three different numbers:

• COUNT(*) — how many rows are in the bucket. NULLs don't matter; rows are rows.
• COUNT(email) — how many rows have a non-NULL email. Blanks are skipped.
• COUNT(DISTINCT email) — how many unique non-NULL emails.

A 100-row table with 10 missing emails and 5 people sharing a family email: COUNT(*) = 100, COUNT(email) = 90, COUNT(DISTINCT email) = 86. Three questions, three answers — and interviewers love asking you to trace exactly this.

Aggregates and NULL — the silent skip

Remember from lesson 1: NULL means unknown. Aggregates deal with it by ignoring NULLs entirely — on both sides of the maths. A bonus column holding (5000, NULL, 3000, NULL, 2000):

SELECT COUNT(*), COUNT(bonus), AVG(bonus)
FROM payouts;

5 | 3 | 3333.33

WHERE vs HAVING — the question you will definitely face

The principal upgrades her demand: "only show classes with more than 60 students." Can you check that condition before forming the buckets? Think about it — the count doesn't exist until the bucket is complete! That's the entire story:

-- WRONG: WHERE runs before grouping — no SUM exists yet
SELECT department, SUM(salary)
FROM employees
WHERE SUM(salary) > 200000   -- ERROR
GROUP BY department;

-- RIGHT: filter the groups with HAVING
SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING SUM(salary) > 200000;

Engineering | 350000
Sales       | 247000

Which columns are you allowed to SELECT?

Here's a query that looks innocent:

SELECT name, department, COUNT(*)
FROM employees
GROUP BY department;  -- ERROR in modern MySQL

The Engineering bucket holds 4 employees — 4 different names. Which name should SQL print? There is no right answer, and that's exactly the point. Old MySQL silently picked one at random (a legendary source of production bugs). Since version 5.7.5, the ONLY_FULL_GROUP_BY mode is on by default and rejects the query. The rule: every selected column is either in the GROUP BY or wrapped in an aggregate. Want a name alongside a group's max salary? That needs a join back or a window function — both covered in later lessons, and one appears in this Practice Zone.

Putting it all together

A full pipeline question: "Among employees hired from 2020 onwards, which departments pay an average above 65,000?" Watch each clause do its one job:

SELECT department, ROUND(AVG(salary), 2) AS avg_salary
FROM employees
WHERE hire_date >= '2020-01-01'   -- 1. guard the gate (rows)
GROUP BY department               -- 2. form buckets
HAVING AVG(salary) > 65000        -- 3. quality-check buckets
ORDER BY avg_salary DESC;         -- 4. sort the survivors

This WHERE → GROUP BY → HAVING → ORDER BY flow is the same execution order you saw in lesson 1's diagram — grouping questions are just that diagram wearing a different shirt.

Common mistakes

• Putting an aggregate condition in WHERE — aggregate conditions live in HAVING.
• Selecting a column that's neither grouped nor aggregated — modern MySQL errors out; older MySQL silently guessed.
• Using HAVING for plain row filters — it works, but it's slower and confusing; row filters belong in WHERE.
• Forgetting that AVG skips NULLs — decide explicitly whether a blank means "unknown" or "zero".
• Joining one-to-many before aggregating and inflating the numbers — the fan-out trap from lesson 2.

Quick recap

Practice Zone — PYQs from real selection rounds

Ten employees, three departments, three cities — and the exact GROUP BY questions companies ask, ending with the classic: highest-paid per department, ties included. Attempt first, reveal second.

Q1COUNT variantseasy

In a 100-row table where the email column is NULL in 10 rows, what does COUNT(email) return?

Asked in

A100 — COUNT always counts rowsB90 — it skips rows where email is NULLC10 — it counts the NULLsDNULL — because some values are NULL

Q2WHERE vs HAVINGeasy

I want only the departments whose total salary crosses ₹10,00,000. Where does that condition go?

Asked in

AWHERE SUM(salary) > 1000000BHAVING SUM(salary) > 1000000 after GROUP BYCEither — WHERE and HAVING are interchangeableDIn the ORDER BY clause

Q3Multi-column GROUP BYmedium

GROUP BY department, city — what forms one group?

Asked in

AEach unique department (city is ignored)BEach unique city (department is ignored)CEach unique (department, city) combination taken togetherDTwo separate sets of groups, one per column

Q4Aggregates and NULLmedium

A bonus column holds (5000, NULL, 3000, NULL, 2000) across 5 rows. What does AVG(bonus) return?

Asked in

A2000 — it divides 10000 by all 5 rowsBAbout 3333.33 — it divides 10000 by the 3 non-NULL rowsCNULL — any NULL poisons the averageDAn error — AVG can't handle NULL

Q5Why WHERE can't aggregatemedium

Fundamentally, why is WHERE SUM(salary) > 100000 illegal?

Asked in

ASUM only works inside SELECT, nowhere elseBWHERE runs before grouping, so no aggregate value exists yetCYou must alias the SUM firstDIt's legal — but only in MySQL

Q6ONLY_FULL_GROUP_BYhard

In modern MySQL (5.7.5+, default settings), what happens to SELECT name, department, COUNT(*) FROM employees GROUP BY department; — where name is neither grouped nor aggregated?

Asked in

AIt returns a random employee's name per departmentBIt fails with an ONLY_FULL_GROUP_BY errorCIt returns NULL for nameDIt returns the alphabetically first name

FAQ

Next lesson: what if the filter itself needs a query to compute? Lesson 4: Subqueries →