Skip to content

Aggregate Functions, GROUP BY, HAVING Class 12 CS SQL

questionbuddy.in/cbse/class-12/computer-science/structured-query-language/aggregate-functions-group-by-and-having

Computer ScienceCBSE Class 12 · Structured Query Language (SQL)

Aggregate Functions, GROUP BY and HAVING in SQL (Class 12 Computer Science)

Updated 17 October 2026 · Fact-checked

Aggregate functions (MAX, MIN, AVG, SUM, COUNT) turn many rows into one value. GROUP BY splits the rows into groups with the same value and gives one result row per group; HAVING keeps only the groups that satisfy a condition, whereas WHERE filters individual rows before grouping.

GROUP BY makes one group per DeptID; HAVING then drops whole groups (D2, D3), while WHERE filters single rows before grouping.

Illustration drawn for QuestionBuddy.

Understand Aggregate Functions, GROUP BY and HAVING

The examples use the EMPLOYEE table of the chapter (six rows; Kiran Shah has Salary NULL).

Aggregate functions work on a column of many rows and return one value.

FunctionMeaning
MAX(col)largest value
MIN(col)smallest value
SUM(col)total
AVG(col)average
COUNT(col)number of non-NULL values
COUNT(*)number of rows, including those with NULLs
SELECT COUNT(*), COUNT(Salary), SUM(Salary), AVG(Salary), MAX(Salary), MIN(Salary) FROM EMPLOYEE;

Result: 6, 5, 241000, 48200.0000, 61000, 38000. Every function except COUNT(*) ignores NULL, so Kiran Shah is not counted by COUNT(Salary), and AVG divides 241000 by 5 (not 6). MySQL shows AVG of an INT column with four decimals. COUNT(DISTINCT City) counts different cities: 4.

GROUP BY. Rows with the same value of the grouping column form a group; the aggregate is computed for each group.

SELECT DeptID, COUNT(*), SUM(Salary) FROM EMPLOYEE GROUP BY DeptID;
DeptIDCOUNT(*)SUM(Salary)
D12113000
D2238000
D3290000

Rule: every column in the SELECT list that is not inside an aggregate must appear in GROUP BY. Group order is not guaranteed unless ORDER BY is used.

HAVING. Applies a condition to groups (usually on an aggregate).

SELECT DeptID, SUM(Salary) FROM EMPLOYEE GROUP BY DeptID HAVING SUM(Salary) > 90000;

Result: D1 with 113000.

WHERE versus HAVING. WHERE filters rows before grouping and cannot contain an aggregate; HAVING filters groups after grouping and normally does. The two can be combined:

SELECT DeptID, COUNT(Salary) FROM EMPLOYEE WHERE City <> 'Delhi' GROUP BY DeptID;

Rows of Delhi are removed first; then each department is counted, giving D1 1, D2 1, D3 1.

Clause order. SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.

Aggregate Functions, GROUP BY and HAVING: key facts

Aggregate functions
MAX, MIN, SUM, AVGignore NULL
COUNT(col)counts non-NULL values
COUNT(star)counts all rows
COUNT(DISTINCT col)different values
Each returns one value.
AVG with NULL
AVG = sum of non-NULL values / number of non-NULL values | EMPLOYEE: 241000 / 5 = 48200
NULL rows are not part of the divisor.
GROUP BY
  • One output row per group
  • Non-aggregate SELECT columns must be in GROUP BY
  • Group order not guaranteed without ORDER BY
Put GROUP BY after WHERE.
WHERE or HAVING
WHERE: filters rows before grouping, no aggregates | HAVING: filters groups after grouping, usually with an aggregate
Clause order: WHERE, GROUP BY, HAVING, ORDER BY.

How to answer Aggregate Functions, GROUP BY and HAVING questions

For GROUP BY output questions, make the groups on paper before evaluating anything.

  1. 1Apply WHERE to the table and keep the surviving rows.
  2. 2Group the survivors by the GROUP BY column.
  3. 3Compute each aggregate per group; remember that functions other than COUNT(*) skip NULL.
  4. 4Apply HAVING to the group results; discard groups that fail.
  5. 5Apply ORDER BY, then write the result with headings.
  6. 6Check the SELECT list: any non-aggregate column must be in GROUP BY.

Aggregate Functions, GROUP BY and HAVING in real life

The idea, met in an everyday situation. Answer the question in your own words, then compare with the model answer. The one-line hook is what to remember.

Salary by department

Suppose the accounts team wants, for each department, the number of staff and the total salary bill.

Write the query and the result for D1, D2 and D3.

Try it first, then show the solutionSolution
  1. Group by DeptID; use COUNT(*) and SUM(Salary).
  2. D2 has a NULL salary, which SUM ignores, so D2 totals 38000.

Answer: SELECT DeptID, COUNT(*), SUM(Salary) FROM EMPLOYEE GROUP BY DeptID; gives D1 2 113000, D2 2 38000, D3 2 90000.

Memory hook: Make piles first, then count each pile.

Only the big departments

Suppose the management is interested only in departments whose salary bill is above 90000.

Which clause is needed and what is the result?

Try it first, then show the solutionSolution
  1. The condition is on a total of a group, so HAVING SUM(Salary) > 90000.
  2. D1 has 113000; D3 has exactly 90000, which is not above 90000.

Answer: HAVING SUM(Salary) > 90000 returns only D1 with 113000.

Memory hook: WHERE judges each person; HAVING judges each team.

Tricks to remember Aggregate Functions, GROUP BY and HAVING

Short memory aids. Say each one aloud once, then use it on a question the same day.

  • COUNT(*) counts rows, COUNT(col) counts values

    The star counts everybody; a column name counts only the filled cells.

    Use it when: NULL questions.

  • WHERE before, HAVING after

    WHERE works before the groups exist; HAVING works after.

    Use it when: Choosing the clause.

  • Group columns must be listed

    Anything not inside an aggregate goes into GROUP BY.

    Use it when: Writing correct queries.

  • AVG skips NULL in the divisor

    Divide by the filled cells, not by all rows.

    Use it when: AVG output questions.

Aggregate Functions, GROUP BY and HAVING: solved examples

Ordered from board level to HOTS. Try each one before you open the solution.

Example 1: COUNT(*) · 1 mark · MCQBoard level

Which function counts all the rows of a table, including rows that contain NULL?
(a) COUNT(Salary) (b) COUNT(*) (c) SUM(*) (d) MAX(*)

Try it first, then show the solutionSolution
  1. COUNT(column) skips NULL; COUNT(*) counts every row.

Answer: (b) COUNT(*)

Example 2: Two aggregate queries · 2 marks · Short answerBoard level

Write queries on EMPLOYEE to display (i) the highest salary, (ii) the number of employees. Give the results.

Try it first, then show the solutionSolution
  1. (i) SELECT MAX(Salary) FROM EMPLOYEE; gives 61000. (1 mark)
  2. (ii) SELECT COUNT(*) FROM EMPLOYEE; gives 6. (1 mark)

Answer: (i) 61000 (ii) 6

Example 3: NULL and aggregates · 3 marks · Short answerHarder board level

Write the output of SELECT COUNT(*), COUNT(Salary), SUM(Salary), AVG(Salary) FROM EMPLOYEE; and explain the difference between the first two values.

Try it first, then show the solutionSolution
  1. COUNT(*) = 6 (all rows); COUNT(Salary) = 5 (Kiran Shah's NULL is skipped). (1.5 marks)
  2. SUM(Salary) = 52000 + 38000 + 61000 + 45000 + 45000 = 241000. (0.5 mark)
  3. AVG(Salary) = 241000 / 5 = 48200 (shown as 48200.0000 in MySQL). (1 mark)

Answer: 6 | 5 | 241000 | 48200.0000

Example 4: GROUP BY output · 3 marks · Short answerHarder board level

Write the output of SELECT DeptID, COUNT(*), SUM(Salary) FROM EMPLOYEE GROUP BY DeptID; and explain why D2 shows 38000.

Try it first, then show the solutionSolution
  1. D1: Asha Rao and Meena Iyer, count 2, sum 52000 + 61000 = 113000. (1 mark)
  2. D2: count 2, sum 38000 because Kiran Shah's NULL salary is ignored by SUM. (1 mark)
  3. D3: Joy Das and Farah Khan, count 2, sum 45000 + 45000 = 90000. (1 mark)

Answer: D1 | 2 | 113000
D2 | 2 | 38000
D3 | 2 | 90000

Example 5: Case: reports for the management · 4 marks · Case-basedHOTS / competency

Suppose the accounts department runs these queries on EMPLOYEE. Write the output of each.
(i) SELECT DeptID, SUM(Salary) FROM EMPLOYEE GROUP BY DeptID HAVING SUM(Salary) > 90000; (1 mark)
(ii) SELECT City, COUNT(*) FROM EMPLOYEE GROUP BY City HAVING COUNT(*) >= 2; (1 mark)
(iii) SELECT DeptID, COUNT(Salary) FROM EMPLOYEE WHERE City <> 'Delhi' GROUP BY DeptID; (1 mark)
(iv) Which of WHERE and HAVING removes rows before the groups are made? (1 mark)

Try it first, then show the solutionSolution
  1. (i) D1 has 113000 and D3 exactly 90000 (not above), so only D1 | 113000.
  2. (ii) Delhi has Asha Rao and Joy Das; Mumbai has Ravi Kumar and Farah Khan; others have one each. Output: Delhi 2, Mumbai 2.
  3. (iii) Delhi rows are removed. D1 keeps Meena (1 salary); D2 keeps Ravi (38000) and Kiran (NULL, not counted) giving 1; D3 keeps Farah, 1. Output: D1 1, D2 1, D3 1.
  4. (iv) WHERE.

Answer: (i) D1 113000 (ii) Delhi 2, Mumbai 2 (iii) D1 1, D2 1, D3 1 (iv) WHERE

Where students lose marks in Aggregate Functions, GROUP BY and HAVING

The slips examiners see most often, and what to write instead.

  • Mistake: Using an aggregate in WHERE

    WHERE works on single rows and cannot use an aggregate.

    Wrong working

    WHERE SUM(Salary) > 90000

    Right working

    GROUP BY DeptID HAVING SUM(Salary) > 90000

    Do this: Move aggregate conditions to HAVING.

    Usually costs: 1 mark

  • Mistake: Dividing AVG by all the rows

    AVG ignores NULL values both in the sum and in the count.

    Wrong working

    241000 / 6 = 40166.67

    Right working

    241000 / 5 = 48200

    Do this: Count only the filled cells.

    Usually costs: 1 mark

  • Mistake: Selecting a column not in GROUP BY

    A plain column in the SELECT list must be grouped.

    Wrong working

    SELECT Name, COUNT(*) FROM EMPLOYEE GROUP BY DeptID;

    Right working

    SELECT DeptID, COUNT(*) FROM EMPLOYEE GROUP BY DeptID;

    Do this: List only grouped columns and aggregates.

    Usually costs: 1 mark

Exam tips

  • For NULL columns, say clearly which rows an aggregate skips.
  • Compute each group on the margin before writing the result.
  • Show AVG as the MySQL decimal form (48200.0000), or as the plain number if the paper uses it.
  • When the question says "for each", the answer needs GROUP BY.
  • Use HAVING for conditions on aggregates and WHERE for conditions on single rows.

Practice questions on Aggregate Functions, GROUP BY and HAVING

Write each answer on paper first. Open the hint only if you are stuck, and the answer only when you have finished.

  1. Q1 · 1 mark · MCQBoard level

    Which clause is used to apply a condition on a group in SQL?
    (a) WHERE (b) ORDER BY (c) HAVING (d) DISTINCT

    Hint

    Think of the aggregate condition.

    Answer

    (c) HAVING

  2. Q2 · 1 mark · Very short answerBoard level

    What is the output of SELECT MIN(DOJ) FROM EMPLOYEE;?

    Hint

    The earliest date is the smallest.

    Answer

    2017-01-20

  3. Q3 · 3 marks · Short answerHarder board level

    Write the output of SELECT DeptID, AVG(Salary) FROM EMPLOYEE GROUP BY DeptID;

    Hint

    AVG ignores NULL within the group.

    Answer

    D1 | 56500.0000
    D2 | 38000.0000
    D3 | 45000.0000. D1: (52000 + 61000) / 2; D2: only Ravi Kumar's 38000 counts, since Kiran Shah is NULL; D3: (45000 + 45000) / 2.

  4. Q4 · 1 mark · Assertion-reasonHarder board level

    Assertion (A): AVG(Salary) of the EMPLOYEE table is 48200.
    Reason (R): AVG ignores NULL values and divides by the number of non-NULL values.
    (a) Both A and R are true and R is the correct explanation of A. (b) Both A and R are true but R is not the correct explanation of A. (c) A is true but R is false. (d) A is false but R is true.

    Hint

    Sum 241000, how many values?

    Answer

    (a) Both are true and R explains A: 241000 / 5 = 48200.

  5. Q5 · 3 marks · Short answerHOTS / competency

    A student writes: SELECT DeptID, MAX(Salary) FROM EMPLOYEE WHERE MAX(Salary) > 50000 GROUP BY DeptID; (i) What is wrong? (ii) Rewrite it to list departments whose highest salary is above 50000, with the output.

    Hint

    An aggregate cannot be in WHERE.

    Answer

    (i) WHERE cannot contain an aggregate function; the condition is on a group. (ii) SELECT DeptID, MAX(Salary) FROM EMPLOYEE GROUP BY DeptID HAVING MAX(Salary) > 50000; Output: D1 | 61000.

Test your recall

Answer in your head or on paper first, then open the card. Pulling an answer out of memory makes it stick far better than re-reading.

  1. 1.Which aggregate functions are in the syllabus?Show answer

    MAX, MIN, AVG, SUM and COUNT.

  2. 2.Does SUM count NULL as zero?Show answer

    No; it ignores NULL.

  3. 3.What does COUNT(*) count?Show answer

    All rows, including those with NULLs.

  4. 4.Which clause groups rows?Show answer

    GROUP BY.

  5. 5.Which clause filters groups?Show answer

    HAVING.

  6. 6.Which clause cannot contain an aggregate?Show answer

    WHERE.

Aggregate Functions, GROUP BY and HAVING: frequently asked questions

What is the difference between WHERE and HAVING?

WHERE filters individual rows before the groups are formed and cannot use aggregates. HAVING filters whole groups after GROUP BY, usually with an aggregate.

What is the difference between COUNT(*) and COUNT(column)?

COUNT(*) counts all rows. COUNT(column) counts only the rows in which that column is not NULL.

How does AVG handle NULL?

AVG ignores NULL values: it adds the non-NULL values and divides by the number of non-NULL values.

Is the order of groups fixed in GROUP BY output?

No. Add ORDER BY if a particular order is needed.