Theory
The principal wants one line per subject
ResultDesk's next request sounds simple: "average score of each subject, worst first, and flag the subjects averaging under 50."
You know AVG from BCA105, but AVG over the whole marks table gives ONE number for the whole college. The principal wants one line per subject: DBMS 61.5, Maths 73.2, ...
You need a way to fold thousands of rows into one summary row per group, then filter and sort those summaries. That is exactly the GROUP BY toolkit.
Theory
Sorting answer sheets into piles
Drop every answer sheet on a table: chaos. Now make one pile per subject, and write one slip per pile: pile name, average, count.
GROUP BY makes the piles. The aggregates write each pile's slip. HAVING throws away entire piles that fail a test (average under 50). ORDER BY arranges the surviving slips for reading. You never look at individual sheets again after the piles form: only slips.
Theory
GROUP BY and HAVING, formally
GROUP BY col collapses all rows sharing a value of col into one output row; every other selected column must be wrapped in an aggregate.
HAVING filters groups after aggregation, and is the only place an aggregate may appear in a condition.
The distinction exams never stop asking:
- WHERE: filters rows, runs before grouping, aggregates forbidden.
- HAVING: filters groups, runs after grouping, aggregates allowed.
Practical
The principal's request, one query
SELECT subject,
AVG(score) AS average,
COUNT(*) AS papers
FROM marks
WHERE score IS NOT NULL -- row filter, BEFORE piles form
GROUP BY subject -- one pile per subject
HAVING AVG(score) < 50 -- keep only weak piles
ORDER BY average ASC; -- worst first
-- grade bands with CASE (if-else inside a query):
SELECT roll, score,
CASE
WHEN score >= 70 THEN 'Distinction'
WHEN score >= 40 THEN 'Pass'
ELSE 'Fail'
END AS grade
FROM marks
WHERE subject = 'DBMS';
This example runs in Gri-Learn on the web, where you can edit it and see the output.
Theory
ORDER BY and CASE, the finishing tools
ORDER BY average ASC sorts the output: ASC (small to large) is the default, DESC reverses. Multiple keys work: ORDER BY subject ASC, score DESC.
CASE is SQL's if-else expression:
CASE WHEN test THEN value WHEN test THEN value ELSE value END
Conditions are checked top to bottom, first match wins: score 85 hits the >= 70 branch and never reaches >= 40. Order your WHENs from strictest to loosest or every row lands in the first loose branch.
Quiz
A student writes: SELECT subject, AVG(score) FROM marks WHERE AVG(score) < 50 GROUP BY subject; and gets an error. Why?
- Aggregates cannot appear in WHERE; group conditions belong in HAVING after GROUP BY
- AVG cannot be used together with GROUP BY
- WHERE must always come after GROUP BY
- The query is missing ORDER BY
Show the answer
Aggregates cannot appear in WHERE; group conditions belong in HAVING after GROUP BY
WHERE runs before the piles form, when AVG(score) does not exist yet, so aggregates are forbidden there. The fix: move the condition to HAVING AVG(score) < 50 after GROUP BY. Option B is backwards (AVG with GROUP BY is the standard pair), option C breaks the fixed clause order (WHERE comes before GROUP BY), and ORDER BY is optional polish, never the cause of this error.
Think first
Trace the grade bands
Scores in DBMS: 85, 62, 38, 70. Using CASE WHEN score >= 70 THEN 'Distinction' WHEN score >= 40 THEN 'Pass' ELSE 'Fail' END, assign each score its grade. Careful with 70 and 38.
Show the answer
85 → Distinction. 62 → Pass. 38 → Fail (fails both tests, falls to ELSE). 70 → Distinction (>= 70 is true AT 70; first match wins and evaluation stops).
The two hinge cases are the boundary (70 counts as Distinction because >= includes equality) and the fall-through (38 reaches ELSE). Exams build entire questions on exactly these two rows.
Watch out
The clause order is not negotiable
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT
Write them in any other order and SQLite errors. Memorise the sequence with the flow itself: filter rows (WHERE), make piles (GROUP BY), filter piles (HAVING), arrange slips (ORDER BY), take the top few (LIMIT). And in a CASE, put the strictest WHEN first: conditions are tested top to bottom.
Theory
Where this resurfaces
Unit 4 replays this exact lesson in pandas: GROUP BY becomes df.groupby('subject'), HAVING becomes a filter on the grouped result, CASE becomes a mapping function. Learn the SQL version cold and the pandas version becomes vocabulary, not a new concept. The weak-subject query you wrote today is also, almost line for line, how result-analysis dashboards flag failing courses.
Summary
Key takeaways
- GROUP BY folds rows sharing a value into one summary row; pair it with aggregates.
- WHERE filters rows BEFORE grouping (no aggregates); HAVING filters groups AFTER (aggregates allowed).
- ORDER BY sorts (ASC default, DESC to reverse), multiple keys allowed.
- CASE WHEN ... THEN ... ELSE ... END = if-else in a query; first match wins, order WHENs strictest first.
- Clause order: SELECT FROM WHERE GROUP BY HAVING ORDER BY LIMIT.
- Memory hook: piles, slips, throw weak piles, arrange the rest.