Practice questions: aggregation and grouping
Aggregation and grouping20 min readLesson 3 of 3
These questions cover the aggregate and grouping rules that exams and interviewers ask about most. Try each one before reading the answer. Several questions have a short query with real output, so you can check your reasoning against the practice database.
3.1.1 Q1 What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?
Answer (English): COUNT(*) counts rows, including rows where every value is NULL. COUNT(column) counts only the rows where that column is not NULL. COUNT(DISTINCT column) counts distinct non-NULL values. On the enrollments table, 14 rows contain 9 ratings because five ratings are NULL, and those 9 ratings contain four distinct values. Pick the count that matches the question and say which NULL rule it follows.
Hinglish: COUNT(*) saari rows ginta hai, COUNT(column) sirf non-NULL values, aur COUNT(DISTINCT column) unique non-NULL values. Enrollments mein 14 rows hain par 9 ratings, kyunki paanch NULL hain, aur un 9 mein se 4 distinct values hain. Jo sawal poochha hai uske hisaab se count chuno.
SELECT COUNT(*) AS all_rows,
COUNT(rating) AS rated_rows,
COUNT(DISTINCT rating) AS distinct_ratings
FROM enrollments;Output
all_rows | rated_rows | distinct_ratings ---------+------------+----------------- 14 | 9 | 4
3.1.2 Q2 What is the difference between WHERE and HAVING?
Answer (English): WHERE filters rows before grouping and can use any stored column. HAVING filters groups after grouping and can use aggregates and grouping expressions. A condition on a single row belongs in WHERE, and a condition on a group's count or sum belongs in HAVING. Using WHERE early also means fewer rows enter the grouping stage, which is why WHERE is usually the cheaper place for a row filter.
Hinglish: WHERE grouping se pehle rows filter karta hai aur koi bhi stored column dekh sakta hai. HAVING grouping ke baad groups filter karta hai aur aggregates dekh sakta hai. Single row ki condition WHERE mein jaye aur group ke count/sum ki condition HAVING mein. WHERE early lagane se grouping mein kam rows jaati hain, isliye woh sasta hota hai.
3.1.3 Q3 Why does an aggregate inside WHERE fail, and how do you rewrite it?
Answer (English): WHERE is evaluated before groups exist, so an aggregate has no group to compute over. SQLite reports "misuse of aggregate". Move the aggregate condition to HAVING and keep only row conditions in WHERE. The rewrite below returns the courses with at least three enrollments. The error is a useful design check: if a condition needs a group value, it belongs after grouping.
Hinglish: WHERE groups banne se pehle chalta hai, isliye aggregate ke paas group hi nahi hota. SQLite "misuse of aggregate" error deta hai. Aggregate condition ko HAVING mein le jao aur WHERE mein sirf row conditions rakho. Neeche wala rewrite un courses ko deta hai jinke kam se kam teen enrollments hain.
-- error demo: an aggregate cannot appear in WHERE
SELECT course_code
FROM enrollments
WHERE COUNT(*) > 1
GROUP BY course_code;
SELECT course_code, COUNT(*) AS n
FROM enrollments
GROUP BY course_code
HAVING COUNT(*) >= 3
ORDER BY n DESC, course_code;Output
ERROR: misuse of aggregate: COUNT() course_code | n ------------+-- SQL | 5 PY | 4 DS | 3
3.1.4 Q4 How does GROUP BY treat NULL values?
Answer (English): GROUP BY treats NULLs as one equal key, so every row with a NULL in the grouping column falls into a single bucket. That bucket appears in the result with NULL in the group column. This is different from = comparisons, where NULL equals nothing. Aggregate functions then skip NULL inputs inside each bucket, so a count of the grouped column can be smaller than the number of rows in the group.
Hinglish: GROUP BY NULL ko ek equal key maanta hai, isliye grouping column mein NULL wali saari rows ek hi bucket mein aati hain aur result mein group column NULL dikhta hai. = comparison mein NULL kisi ke barabar nahi hota, par grouping mein NULL ek group baanta hai. Us bucket ke andar aggregates NULL inputs skip karte hain.
SELECT rating, COUNT(*) AS n
FROM enrollments
GROUP BY rating
ORDER BY rating;Output
rating | n -------+-- NULL | 5 2 | 1 3 | 2 4 | 3 5 | 3
3.1.5 Q5 Is it valid to select a column that is not in GROUP BY?
Answer (English): Standard SQL rejects it because one group can hold several values for that column, and the statement does not say which one to return. SQLite accepts it and picks an arbitrary row's value, while PostgreSQL rejects it and MySQL rejects it under ONLY_FULL_GROUP_BY. Some engines also accept columns that are functionally dependent on the group key, such as a primary key. Write the query so the chosen value is explicit, using MIN or MAX.
Hinglish: Standard SQL reject karta hai, kyunki ek group mein us column ki kai values ho sakti hain aur query nahi batati kaunsi lena hai. SQLite arbitrary row ka value utha leta hai, PostgreSQL ONLY_FULL_GROUP_BY ke saath error deta hai. Kuch engines primary key jaisi functional dependency allow karte hain. Value explicit chahiye to MIN ya MAX use karo.
3.1.6 Q6 How does AVG treat NULLs, and how do you average with NULL as zero?
Answer (English): AVG ignores NULL inputs and divides the sum by the number of non-NULL values. The average rating over 9 known ratings is 3.89, while the table has 14 rows. If business rules say a missing rating counts as zero, wrap the column with COALESCE inside the aggregate: AVG(COALESCE(rating, 0)). That changes both the numerator and the denominator, so state the rule in the report.
Hinglish: AVG NULL inputs skip karta hai aur sum ko non-NULL values ki ginti se divide karta hai. 9 known ratings ka average 3.89 hai jabki table mein 14 rows hain. Agar missing rating ko zero maana jaye to AVG(COALESCE(rating, 0)) use karo; isse numerator aur denominator dono badalte hain, isliye report mein rule likho.
SELECT COUNT(*) AS all_rows,
COUNT(rating) AS rated_rows,
AVG(rating) AS avg_known,
AVG(COALESCE(rating, 0)) AS avg_with_zero
FROM enrollments;Output
all_rows | rated_rows | avg_known | avg_with_zero ---------+------------+-------------------+-------------- 14 | 9 | 3.888888888888889 | 2.5
3.1.7 Q7 What does a global aggregate return when the filter matches no rows, and what does a grouped aggregate return?
Answer (English): A global aggregate without GROUP BY returns one row. COUNT returns 0, SUM and AVG return NULL, and MIN and MAX return NULL. A grouped aggregate over no rows returns zero rows, because there are no groups to describe. That difference matters in application code: a missing row and a row of NULLs need different handling, and a dashboard should show 0 rather than blank when the count is the metric.
Hinglish: Bina GROUP BY wala global aggregate khaali input par bhi ek row deta hai: COUNT 0, SUM/AVG NULL. GROUP BY wala aggregate khaali input par zero rows deta hai, kyunki koi group hi nahi bana. Application code mein missing row aur NULL row ko alag handle karna padta hai, aur dashboard par count ke liye 0 dikhna chahiye.
3.1.8 Q8 Group enrollments by calendar month and show the count.
Answer (English): Use strftime('%Y-%m', enrolled_on) as the grouping expression, count the rows, and sort by the month string. The ISO month format sorts correctly as text. The output below has 3 enrollments in January, 5 in February and 6 in March. Grouping by the formatted expression is portable; grouping by a select alias is accepted by SQLite and PostgreSQL but is not the only accepted spelling in every engine.
Hinglish: Grouping expression strftime('%Y-%m', enrolled_on) use karo, rows count karo aur month string par sort karo. ISO month text ki tarah sahi sort hota hai. Output mein January 3, February 5 aur March 6 enrollments hain. Formatted expression par group karna portable hai; select alias par group karna SQLite aur PostgreSQL mein chalta hai par har engine mein nahi.
SELECT strftime('%Y-%m', enrolled_on) AS month, COUNT(*) AS enrollments
FROM enrollments
GROUP BY strftime('%Y-%m', enrolled_on)
ORDER BY month;Output
month | enrollments --------+------------ 2026-01 | 3 2026-02 | 5 2026-03 | 6
3.1.9 Q9 How do you group by two columns and keep only groups with more than two rows?
Answer (English): List both columns in GROUP BY, count the rows in each pair, then filter with HAVING on the count. The output below has only the (course, status) pairs that occur at least twice, which are SQL active, SQL completed, PY active and DS active. Filtering on the pair count is different from filtering one column at a time, and the difference shows up in reports that mix course totals with status totals.
Hinglish: Dono columns GROUP BY mein likho, har pair ke rows count karo, phir HAVING se count filter karo. Neeche sirf woh (course, status) pairs hain jinke kam se kam do rows hain. Pair ke count par filter karna ek column ke filter se alag hai, aur report mein isi wajah se totals match nahi karte.
SELECT course_code, status, COUNT(*) AS n
FROM enrollments
GROUP BY course_code, status
HAVING COUNT(*) >= 2
ORDER BY course_code, status;Output
course_code | status | n ------------+-----------+-- DS | active | 3 PY | active | 2 SQL | active | 3 SQL | completed | 2
3.1.10 Q10 When should you use GROUP BY instead of DISTINCT?
Answer (English): Both can remove duplicates, but they answer different questions. DISTINCT returns the distinct combinations of the selected columns and cannot compute a summary. GROUP BY defines buckets and lets you attach aggregates to each one, so it is the choice when the output needs a count, sum or average. For pure duplicate removal, DISTINCT states the intent more directly. Under the hood both usually need a sort or hash, so they are closer in cost than they look.
Hinglish: Dono duplicates hata sakte hain, par sawal alag hai. DISTINCT selected columns ke unique combinations deta hai aur summary compute nahi kar sakta. GROUP BY buckets banata hai aur har bucket par aggregates lagata hai, isliye count, sum ya average chahiye to GROUP BY. Sirf duplicate hatane ho to DISTINCT intention zyada clearly batata hai. Dono ke peeche aksar sort ya hash hota hai.
3.2 Quiz pointer
The five-question quiz for this module is separate from these answers. Take it without scrolling back, then rerun the queries you missed. When an option sounds right, check whether it names the clause order and the NULL rule as well, because the quiz explanation scores the same ideas.