Contribute OA questions
OAHelper
CompaniesProblemsTopicsInterview Experiences
Explore
Week 01

Week 01

Aggregation, GROUP BY & HAVING

Day 22 to 3 hrs

SELECT, WHERE, ORDER BY & LIMITAggregation, GROUP BY & HAVINGCASE WHEN, Conditional Aggregation & PivotsINNER & LEFT JOIN: Combining TablesSelf Joins, Anti-Joins & Existence ChecksSubqueries, CTEs & Set Operations

Decide what one result row represents

"For each customer, show the number of orders" and "show customers with at least three orders" use the same grouping, but the second also filters the groups.

That is the main decision in this lesson: which conditions apply to individual rows, and which apply to a total or count? We will also look at missing values, empty groups and ties.

Grouping and aggregates

An aggregate summarises several rows as one value. GROUP BY sets the level of that summary: one row per student, department, month, or any other key. Without GROUP BY, an aggregate summarises all qualifying rows together.

Use this one table for every example. Each row is one paper written by one student, and the student's name and branch are stored on the row itself:

marks

rollnamebranchsubjectmarks
1RahulCSEDBMS78
1RahulCSEOS91
1RahulCSECN64
2SnehaCSEDBMS88
2SnehaCSEOS95
3ArjunECEDBMS55
3ArjunECECNNULL
4DivyaECEOS72

Arjun has a CN row but no recorded mark. Keep this in mind: counting paper rows and counting available marks answer different questions.

Count rows and recorded values

SQL
SELECT COUNT(*) AS rows_counted FROM marks;
rows_counted
8

No GROUP BY, so the entire table is one group, and the group has eight rows.

NULL changes the denominator

SQL
SELECT COUNT(*) AS rows_counted, COUNT(marks) AS marks_present, AVG(marks) AS avg_marks
FROM marks;
rows_countedmarks_presentavg_marks
8777.5714285714286

Eight rows exist, but only seven of them actually carry a mark. COUNT(*) counted the row, COUNT(marks) counted the value, and AVG divided by seven, not by eight.

Summarise per student

GROUP BY roll forms one group per roll number, then evaluates each aggregate within that group. It does not guarantee the order of the result.

SQL
SELECT roll, COUNT(*) AS papers, SUM(marks) AS total
FROM marks
GROUP BY roll
ORDER BY roll;
rollpaperstotal
13233
22183
3255
4172

Roll 3 shows 2 papers but a total of only 55, because COUNT(*) counted his NULL row while SUM ignored the NULL value inside it.

ORDER BY at the end is not decoration. Group order is not guaranteed by the database, so you fix it yourself.

Summarise per branch

Branch is a column of marks, so grouping by it works exactly like grouping by roll.

SQL
SELECT branch, COUNT(*) AS papers, AVG(marks) AS avg_marks
FROM marks
GROUP BY branch
ORDER BY branch;
branchpapersavg_marks
CSE583.2
ECE363.5

CSE has five paper rows because Rahul wrote three and Sneha wrote two. ECE also has three rows, but its average is taken over only two marks, because Arjun's CN mark is NULL and AVG skips it.

The main forms used here are COUNT(*), COUNT(col), COUNT(DISTINCT col), SUM, AVG, MIN and MAX.

IMP

Important: COUNT(*) counts rows. COUNT(col) counts rows where col is not NULL. SUM, AVG, MIN and MAX also skip NULLs completely. Choose the counted column according to what the question means by a record.

  1. 1Rows from FROM
  2. 2WHERE drops rows
  3. 3GROUP BY makes groups
  4. 4Aggregate runs per group
  5. 5HAVING drops groups
  6. 6ORDER BY and LIMIT

The Pattern

Build the query around its grouping key.

  1. Read the required output columns. Identify the aggregate values and the columns that define one result row.
  2. Put every non-aggregate output column into the GROUP BY.
  3. Row level conditions, the ones you can judge by looking at a single row, go in WHERE, before grouping.
  4. Group level conditions, the ones you cannot judge until the group exists, go in HAVING, after grouping. HAVING is simply WHERE for groups; it exists because WHERE runs too early to see a COUNT.
  5. ORDER BY last, always with a tie break column, so the output is deterministic.

Here is the query we will dry run.

SQL
SELECT name, COUNT(*) AS papers, SUM(marks) AS total
FROM marks
WHERE marks >= 60
GROUP BY roll, name
HAVING COUNT(*) >= 2
ORDER BY total DESC, name ASC;

Trace the conditions.

  1. FROM marks reads every paper row.
  2. WHERE marks >= 60 throws away individual paper rows scoring below 60, one row at a time, before any grouping happens.
  3. GROUP BY roll, name puts the surviving rows into one group per student. Roll is in the list because it is unique, name is in the list because we print it.
  4. HAVING COUNT(*) >= 2 looks at each finished group and drops the groups with fewer than two rows.
  5. SELECT name, COUNT(*), SUM(marks) prints one row per surviving group.
  6. ORDER BY total DESC, name ASC sorts by total, highest first, and uses the name to break ties.

Now the same query as a table, one line per step, so you can see how many rows are alive at each point.

StepWhat survivesRows
FROM marksRahul x3, Sneha x2, Arjun x2, Divya x18
WHERE marks >= 60Arjun's 55 goes, and his NULL CN goes too6
GROUP BY rollgroup Rahul(3), group Sneha(2), group Divya(1)3 groups
AggregateRahul 233, Sneha 183, Divya 723
HAVING papers >= 2Divya's group is dropped2
ORDER BY total DESCRahul first, then Sneha2

Final output:

namepaperstotal
Rahul3233
Sneha2183

Arjun is excluded by WHERE because none of his marks meet the cutoff. Divya has a qualifying mark, but HAVING removes her group because she has fewer than two qualifying papers.

Also note the NULL. NULL >= 60 is not true and not false, it is unknown, so WHERE drops Arjun's absent CN row without you asking.

What the wrong version looks like. Students often skip the HAVING line because the sample output happens to match. Drop that one line and this comes back instead:

namepaperstotalnote
Rahul3233correct
Sneha2183correct
Divya172extra row, should not be here

The extra row shows why both conditions are needed, even if one happens not to affect a given sample.

Check what you learned

Using the marks sheet above, you need "only DBMS records" and "only students with at least two such records". Where does each condition go?

Query Templates

SQL

SQL
-- 1. Plain aggregate over a filtered table. There is no "per each", so no GROUP BY.
SELECT COUNT(*) AS absent_papers
FROM marks
WHERE marks IS NULL;

-- 2. The workhorse: per group numbers, with a row filter and a group filter.
SELECT branch, COUNT(*) AS papers, ROUND(AVG(marks), 2) AS avg_marks
FROM marks
WHERE marks IS NOT NULL
GROUP BY branch
HAVING AVG(marks) >= 70
ORDER BY avg_marks DESC, branch ASC;

-- 3. Top N by an aggregate. Tie break first, then LIMIT.
SELECT name, SUM(marks) AS total
FROM marks
GROUP BY roll, name
ORDER BY total DESC, name ASC
LIMIT 3;

Template 1 answers "how many papers were missed", which needs a single number, so there is no grouping key at all. Template 2 returns CSE with 5 papers and 83.2; ECE averages 63.5 over its two recorded marks and is dropped by the HAVING. LIMIT in template 3 simply cuts the result after n rows once the sort is done, so it returns Rahul 233, Sneha 183 and Divya 72.

Check whether the assessment requires a particular row order or fixed decimal formatting. Those requirements vary by platform.

Variations

Variation 1 - Finding repeats

Group by the column you suspect and keep only the groups bigger than one. This is the standard duplicate hunting query, and in an OA it usually appears as "find the duplicate email ids" or "find the phone numbers registered more than once".

SQL
SELECT name, COUNT(*) AS papers
FROM marks
GROUP BY roll, name
HAVING COUNT(*) > 1
ORDER BY papers DESC, name ASC;
namepapers
Rahul3
Arjun2
Sneha2

Arjun is here with 2 even though one of his marks is NULL, because COUNT(*) counts the row and not the value. Divya is absent because her group has size 1.

Variation 2 - COUNT versus COUNT(DISTINCT)

The moment the statement says "different", "distinct" or "unique", the DISTINCT goes inside the aggregate. DISTINCT inside COUNT means "count each repeated value only once".

SQL
SELECT branch, COUNT(subject) AS papers, COUNT(DISTINCT subject) AS subjects
FROM marks
GROUP BY branch
ORDER BY branch;
branchpaperssubjects
CSE53
ECE33

CSE wrote five papers but across only three subjects, because Rahul and Sneha both took DBMS and both took OS.

If the question was "how many subjects does each branch cover", COUNT(*) would have answered 5 for CSE and failed the case.

Variation 3 - Comparing a group against the whole table

For branches above the overall average, compute the overall figure in a subquery and compare each branch against it.

SQL
SELECT branch, ROUND(AVG(marks), 2) AS branch_avg
FROM marks
GROUP BY branch
HAVING AVG(marks) > (SELECT AVG(marks) FROM marks)
ORDER BY branch_avg DESC;

The inner query returns 77.5714, and each branch average is compared against it, so only CSE survives:

branchbranch_avg
CSE83.2

Note this point: compare on the raw AVG(marks), not on the rounded value. If you round first, a group sitting exactly on the boundary can flip to the wrong side.

Variation 4 - The top group, ties included

If the question asks for every subject tied for the highest record count, LIMIT 1 is not enough. Here DBMS has 3 records, OS has 3 and CN has 2.

ORDER BY attempts DESC LIMIT 1 returns one winner and omits the other. Without a secondary sort key, even the choice of winner is not guaranteed.

  1. 1Count per subject
  2. 2Take the MAX of those counts
  3. 3Keep every subject equal to it
  4. 4All tied rows come back
SQL
SELECT subject, COUNT(*) AS attempts
FROM marks
GROUP BY subject
HAVING COUNT(*) = (SELECT MAX(c) FROM (SELECT COUNT(*) AS c FROM marks GROUP BY subject))
ORDER BY subject;
subjectattempts
DBMS3
OS3

The innermost query produces the three counts, MAX picks 3 out of them, and the HAVING keeps every subject whose count equals 3. Use LIMIT 1 only when the statement clearly says to return a single row.

Check what you learned

For CSE the five marks are 78, 91, 64, 88, 95. What do SUM(marks)/COUNT(*) and AVG(marks) print?

Common Mistakes

  • Using an aggregate in WHERE. WHERE COUNT(*) > 1 is invalid for this grouped query. Put the count condition in HAVING.
  • Selecting an unrelated column. If you group by branch, which subject should a bare subject column display? SQLite may accept it and choose an arbitrary value. Group or aggregate it according to the required result.
  • Calculating an average with the wrong denominator. SUM(marks) / COUNT(*) counts missing marks in the denominator and may also perform integer division. Use AVG(marks) for the mean of recorded marks.
  • Counting rows when the question counts marks. For Arjun, COUNT(*) returns 2 because he has two paper rows. COUNT(marks) returns 1 because only one of them has a recorded mark. Pick the one the statement asks for.
  • Relying on GROUP BY for sorting. Use ORDER BY when the result needs a defined order.
  • Sorting by a rounded value. Display a rounded average if required, but sort by the raw average when the question ranks the actual values.
  • Removing NULL groups automatically. GROUP BY collects NULL keys into one group. Keep or exclude that group based on the requirement.
  • Expecting SUM over no values to return zero. It returns NULL. Use COALESCE(SUM(marks), 0) when zero is the intended result.

Interview Notes

What is the difference between WHERE and HAVING? WHERE filters individual rows before grouping happens, HAVING filters whole groups after the aggregates have been computed. Put a condition in WHERE when it genuinely applies to individual rows; this also reduces the rows that need grouping. A condition on the group total belongs in HAVING.

Can you use HAVING without GROUP BY? Yes. With no GROUP BY the entire table is treated as one single group, so SELECT COUNT(*) FROM marks HAVING COUNT(*) > 5 is perfectly legal. It is rare in practice, but interviewers ask it to see whether you understand that a missing GROUP BY still means one group.

COUNT(*) versus COUNT(col) versus COUNT(1)? COUNT(*) and COUNT(1) both count rows. Prefer COUNT(*) for clarity; check the query plan before making performance claims. COUNT(col) counts only the rows where that column is not NULL, which is a different answer, not a faster one.

Does COUNT(DISTINCT col) count NULL? No. NULL is skipped entirely and is never treated as one of the distinct values. For a text column, COUNT(DISTINCT COALESCE(col, 'unknown')) can count missing values as a category, provided 'unknown' cannot also be a real value.

What is the logical order of the clauses? FROM, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY, then LIMIT. This one line explains a lot of behaviour, for example why a SELECT alias works inside ORDER BY but not inside WHERE.

Can HAVING refer to a SELECT alias? SQLite and MySQL allow it, Postgres and Oracle do not, because SELECT is evaluated after HAVING in the standard. If you want the query to be portable, repeat the expression instead of using the alias.

How do you return all the rows tied at the top? Compute the maximum in a subquery and keep every group whose aggregate equals it, or use RANK() once you reach window functions in Week 2. LIMIT 1 gives you one arbitrary winner and quietly hides the tie.

What does AVG do with NULLs? It skips them, so the denominator is the number of non NULL values and not the row count. If absent students must count as zero, say so explicitly with AVG(COALESCE(marks, 0)).

How would you make a GROUP BY query faster? Apply valid row filters before grouping. An appropriate index may help, depending on the query and execution plan. For frequently repeated reports, a maintained summary table can reduce work, but it adds a data-refresh requirement.

Quick Test

Check what you learned

Which query correctly returns the subjects attempted by at least three students?

1/5

Day 2

Finished this topic?

Mark it done and your roadmap moves to the next day.

Up nextCASE WHEN, Conditional Aggregation & Pivots
PreviousSELECT, WHERE, ORDER BY & LIMIT

On this page

Summarise with AI

  • ChatGPT
  • Claude
  • Perplexity
  • Grok
  • Google AI Mode
Your next OA is closer than you think.Real questions from this season's OAs
ProductCompany OAsAll ProblemsOA CalendarMock OAsDSA RoadmapSQL RoadmapAgent Engineering
ResourcesInterview experiencesTopicsOA GroupsContributeCompany InsightsOA StorePremiumFor Colleges
OA Helper, powered by NxtWave
TermsPrivacyRefundsTrust & SafetyContact© 2026 OA Helper

Disclaimer: OAHelper is an independent educational platform. We (oahelper.in) do not own the images or questions shown. Content is uploaded by users.