• Practice
  • About 13 min
  • 5 practice questions

Totals and groups: aggregate and GROUP BY practice

Solve exam-style questions with COUNT, SUM, AVG, MAX, MIN, GROUP BY and HAVING on a sales table.

If something looks wrong, please tell us.

Questions about 'total per category' or 'highest in each group' are very common. The hard part is not the SQL but keeping track of numbers. Write the rows of each group separately, calculate, and then compare with the options.

Reminder of the rules

  • COUNT(*) counts rows; SUM adds; AVG gives the sum divided by the number of values; MAX and MIN give extremes.
  • GROUP BY makes one result row per group.
  • WHERE filters rows before grouping; HAVING filters groups after.
  • An expression such as Qty * Rate is calculated for each row before SUM adds it up.

The practice table

SALES(Item, Cat, Qty, Rate), all values invented: (Pen, Stationery, 50, 10), (Notebook, Stationery, 30, 40), (Bag, Accessories, 5, 450), (Bottle, Accessories, 12, 120), (Ruler, Stationery, 40, 15), (Cap, Accessories, 8, 90).

Drills with full working

Drill 1. SELECT Cat, COUNT(*), SUM(Qty) FROM SALES GROUP BY Cat; Stationery has Pen, Notebook, Ruler: 3 rows and 50 + 30 + 40 = 120 pieces. Accessories has Bag, Bottle, Cap: 3 rows and 5 + 12 + 8 = 25 pieces. Output: (Accessories, 3, 25), (Stationery, 3, 120).

Drill 2. SELECT Cat, SUM(Qty * Rate) FROM SALES GROUP BY Cat; Stationery: 50 x 10 = 500, 30 x 40 = 1200, 40 x 15 = 600, total 2300. Accessories: 5 x 450 = 2250, 12 x 120 = 1440, 8 x 90 = 720, total 4410. Output: (Accessories, 4410), (Stationery, 2300).

Drill 3. SELECT Cat, MAX(Rate), MIN(Rate) FROM SALES GROUP BY Cat; Accessories rates are 450, 120, 90, so (450, 90). Stationery rates are 10, 40, 15, so (40, 10).

Drill 4. SELECT Cat, AVG(Rate) FROM SALES GROUP BY Cat HAVING AVG(Rate) > 100; Accessories: (450 + 120 + 90) / 3 = 660 / 3 = 220. Stationery: (10 + 40 + 15) / 3 = 65 / 3, about 21.67. Only Accessories passes. Output: (Accessories, 220).

Drill 5. SELECT Cat, COUNT(*) FROM SALES WHERE Rate >= 40 GROUP BY Cat; WHERE removes cheap rows first: Pen (10) and Ruler (15) go. Stationery keeps only Notebook, count 1. Accessories keeps all three (450, 120, 90), count 3. Output: (Accessories, 3), (Stationery, 1).

Drill 6. SELECT Cat, SUM(Qty) FROM SALES GROUP BY Cat HAVING SUM(Qty) > 60; Group sums are 25 and 120. Only Stationery exceeds 60. Output: (Stationery, 120).

Drill 7. SELECT SUM(Qty Rate), COUNT() FROM SALES; The grand total is 2300 + 4410 = 6710, and there are 6 rows. Output: (6710, 6).

Group first in your head: list each group's rows, then do the arithmetic once per group.

— Remember

A quick self-check method

After you get an answer, test it. The group counts must add up to the total number of rows (3 + 3 = 6 here). The group totals must add up to the grand total (2300 + 4410 = 6710). If a check fails, one group has been mis-added. This takes ten seconds and catches most slips in an exam.

Common slips

  • Using SUM(Rate) when the question wants total value, which needs SUM(Qty * Rate).
  • Applying HAVING to a single row condition. Use WHERE for rows.
  • Forgetting that the AVG of group averages is not the same as the average of all rows.
  • Writing a non-grouped column in SELECT.

Key takeaways

  • Row calculations come before the aggregate.
  • WHERE then GROUP BY then HAVING is the logical order.
  • Check boundaries: 'more than 60' excludes 60.

Check your understanding

  1. 1. In the SALES table, what is SUM(Qty) for the Stationery group?

    1. 120
    2. 90
    3. 100
    4. 80
    Show answer

    Answer: A. 50 + 30 + 40 = 120.

  2. 2. What is the value of SUM(Qty * Rate) for Accessories?

    1. 2250
    2. 3600
    3. 4410
    4. 1440
    Show answer

    Answer: C. 2250 + 1440 + 720 = 4410.

  3. 3. Which clause can use the condition SUM(Qty) > 60?

    1. WHERE
    2. ORDER BY only
    3. FROM
    4. HAVING
    Show answer

    Answer: D. A condition on an aggregate belongs in HAVING, which filters groups.

  4. 4. The AVG(Rate) for Accessories is

    1. 110
    2. 220
    3. 150
    4. 450
    Show answer

    Answer: B. (450 + 120 + 90) / 3 = 660 / 3 = 220.

  5. 5. How many rows does SELECT Cat, COUNT(*) FROM SALES GROUP BY Cat return?

    1. 6
    2. 3
    3. 1
    4. 2
    Show answer

    Answer: D. There are two categories, so there are two output rows.

Privacy · Terms · Refunds · Delivery