SQL practice

SQL GROUP BY practice questions

9 problems, ordered from easiest. They run in your browser, and 2 are free.

Start with Employee salary aggregates

The idea in a minute

GROUP BY turns many rows into one row per group. You pick the column to group by, and then say what to calculate for each group: how many rows, the total, the average.

Most questions in this group are the same shape: group, count or sum, then keep or sort the groups you care about. The problems below start with a single summary row and build up to HAVING and groups made from several columns.

Example: Total and count per city

CREATE TABLE orders AS SELECT * FROM (VALUES
 (1,'Asha','Pune',450),(2,'Ravi','Pune',300),(3,'Meera','Delhi',900),(4,'Asha','Delhi',150),(5,'Ravi','Pune',250)
) t(order_id, customer, city, amount);
SELECT city, COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders
GROUP BY city
ORDER BY revenue DESC

What it prints

city   orders  revenue
Delhi  2       1050
Pune   3       1000

What to watch for

  • Every column in SELECT must either be in GROUP BY or be inside an aggregate such as SUM or COUNT.
  • WHERE removes rows before grouping. HAVING removes whole groups after grouping.
  • COUNT(*) counts rows, COUNT(column) skips NULLs.

Aggregation problems

  1. Employee salary aggregatesbeginner · Free
  2. Departments with more than three peoplebeginner · Free
  3. Conditional category revenueintermediate · Locked
  4. Ordered in every month of Q1 2024intermediate · Locked
  5. Points table from match resultsintermediate · Locked
  6. Friend request acceptance rate by monthintermediate · Locked
  7. Histogram of comments per userintermediate · Locked
  8. Median employee salaryadvanced · Locked
  9. Modal salaryadvanced · Locked

Want a guided order across every topic? Open the practice sheets or the SQL hub.