Practice: aggregates and grouping
A review of this module. Skip it if you feel confident; the next module does not need it.
Fix the mistakes
The slips that cost marks in this module. Spot each one, then check the fix.
Most grouping bugs come from four habits: selecting a column that is not grouped, putting a filter in the wrong clause, forgetting what NULL does to counts and averages, and counting rows when the question was about something else. Each has a short, reliable fix.
Mistake 1: a selected column that is not grouped
Adding "just one more column" to a working report can break it with error 1055. The example fixes it with GROUP_CONCAT(DISTINCT ...) and COUNT(DISTINCT ...).
Example 1 · Fixing 1055 with GROUP_CONCAT(DISTINCT ...)
DROP TABLE IF EXISTS opd_visits;CREATE TABLE opd_visits ( visit_id INT PRIMARY KEY, doctor VARCHAR(20) NOT NULL, department VARCHAR(20) NOT NULL, fee DECIMAL(7,2) NULL, -- NULL: free health-camp visit status VARCHAR(10) NOT NULL -- DONE or NO_SHOW);
INSERT INTO opd_visits VALUES (1, 'Dr. Rao', 'Cardiology', 800.00, 'DONE'), (2, 'Dr. Rao', 'Cardiology', 800.00, 'DONE'), (3, 'Dr. Iyer', 'Cardiology', 1000.00, 'NO_SHOW'), (4, 'Dr. Iyer', 'Cardiology', 1000.00, 'DONE'), (5, 'Dr. Khan', 'Orthopaedics', 600.00, 'DONE'), (6, 'Dr. Khan', 'Orthopaedics', NULL, 'DONE'), (7, 'Dr. Bose', 'Orthopaedics', 700.00, 'NO_SHOW'), (8, 'Dr. Mehta', 'Paediatrics', 500.00, 'DONE'), (9, 'Dr. Mehta', 'Paediatrics', NULL, 'DONE'), (10, 'Dr. Mehta', 'Paediatrics', 500.00, 'DONE'), (11, 'Dr. Sen', 'Dermatology', 900.00, 'NO_SHOW'), (12, 'Dr. Sen', 'Dermatology', 900.00, 'NO_SHOW');
-- Wrong (error 1055): SELECT department, doctor, COUNT(*) ... GROUP BY department-- Right: decide what "doctor" should show for a whole departmentSELECT department, COUNT(*) AS visits, COUNT(DISTINCT doctor) AS doctors, GROUP_CONCAT(DISTINCT doctor ORDER BY doctor SEPARATOR ', ') AS doctor_listFROM opd_visitsGROUP BY departmentORDER BY department;+--------------+--------+---------+--------------------+ | department | visits | doctors | doctor_list | +--------------+--------+---------+--------------------+ | Cardiology | 4 | 2 | Dr. Iyer, Dr. Rao | | Dermatology | 2 | 1 | Dr. Sen | | Orthopaedics | 3 | 2 | Dr. Bose, Dr. Khan | | Paediatrics | 3 | 1 | Dr. Mehta | +--------------+--------+---------+--------------------+
What this code does
- A department has several visits and possibly several doctors, so a plain
doctorcolumn has no single value per group and MySQL raises error 1055. COUNT(DISTINCT doctor)gives how many doctors, andGROUP_CONCAT(DISTINCT doctor ...)lists each one once. WithoutDISTINCT, Dr. Mehta would be listed three times.
The fix is a decision: one row per value too, add it to GROUP BY; one summary value, use MIN, MAX or COUNT; all the values, use GROUP_CONCAT. Never turn off ONLY_FULL_GROUP_BY to hide the error.
Fix the error
Fix the grouped query
Read the error, then pick the line that fixes it.
Pick the line that replaces line 1 so the query works.
Error
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'practice.employees.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Mistake 2: a filter in the wrong clause
A missing row filter fails quietly: no-shows are counted as earnings and the report looks normal. The example runs a wrong query and the right one, with WHERE before GROUP BY and HAVING after.
Example 2 · No-shows counted as earnings
DROP TABLE IF EXISTS opd_visits;CREATE TABLE opd_visits ( visit_id INT PRIMARY KEY, doctor VARCHAR(20) NOT NULL, department VARCHAR(20) NOT NULL, fee DECIMAL(7,2) NULL, -- NULL: free health-camp visit status VARCHAR(10) NOT NULL -- DONE or NO_SHOW);
INSERT INTO opd_visits VALUES (1, 'Dr. Rao', 'Cardiology', 800.00, 'DONE'), (2, 'Dr. Rao', 'Cardiology', 800.00, 'DONE'), (3, 'Dr. Iyer', 'Cardiology', 1000.00, 'NO_SHOW'), (4, 'Dr. Iyer', 'Cardiology', 1000.00, 'DONE'), (5, 'Dr. Khan', 'Orthopaedics', 600.00, 'DONE'), (6, 'Dr. Khan', 'Orthopaedics', NULL, 'DONE'), (7, 'Dr. Bose', 'Orthopaedics', 700.00, 'NO_SHOW'), (8, 'Dr. Mehta', 'Paediatrics', 500.00, 'DONE'), (9, 'Dr. Mehta', 'Paediatrics', NULL, 'DONE'), (10, 'Dr. Mehta', 'Paediatrics', 500.00, 'DONE'), (11, 'Dr. Sen', 'Dermatology', 900.00, 'NO_SHOW'), (12, 'Dr. Sen', 'Dermatology', 900.00, 'NO_SHOW');
-- Wrong: no-show visits are added to the earningsSELECT department, SUM(fee) AS earnedFROM opd_visitsGROUP BY departmentHAVING SUM(fee) >= 1000ORDER BY department;
-- Right: completed visits only, then the group conditionSELECT department, SUM(fee) AS earnedFROM opd_visitsWHERE status = 'DONE'GROUP BY departmentHAVING SUM(fee) >= 1000ORDER BY department;+--------------+---------+ | department | earned | +--------------+---------+ | Cardiology | 3600.00 | | Dermatology | 1800.00 | | Orthopaedics | 1300.00 | | Paediatrics | 1000.00 | +--------------+---------+ +-------------+---------+ | department | earned | +-------------+---------+ | Cardiology | 2600.00 | | Paediatrics | 1000.00 | +-------------+---------+
What this code does
- The wrong query looks fine but lists Dermatology with Rs 1,800, earned entirely from two no-shows, and adds Rs 1,000 to Cardiology for a patient who never came.
- The right query removes no-shows in
WHEREbefore grouping;HAVINGthen keeps departments that earned at least Rs 1,000: Cardiology and Paediatrics.
A row condition belongs in WHERE, a group condition in HAVING. An aggregate in WHERE fails with error 1111; a plain ungrouped column in HAVING fails with error 1054 ('having clause'), so move it to WHERE.
Find the bug
Find the wrong line
Select the line that is wrong.
This should total delivered orders only, but cancelled orders are included. Which line is wrong?
Round 1 of 2Mistake 3: forgetting what NULL does
A free health-camp visit has a NULL fee. The example counts the visits and averages the fees in two ways, and each way answers a different question.
Example 3 · Free visits: counts and averages with NULL fees
DROP TABLE IF EXISTS opd_visits;CREATE TABLE opd_visits ( visit_id INT PRIMARY KEY, doctor VARCHAR(20) NOT NULL, department VARCHAR(20) NOT NULL, fee DECIMAL(7,2) NULL, -- NULL: free health-camp visit status VARCHAR(10) NOT NULL -- DONE or NO_SHOW);
INSERT INTO opd_visits VALUES (1, 'Dr. Rao', 'Cardiology', 800.00, 'DONE'), (2, 'Dr. Rao', 'Cardiology', 800.00, 'DONE'), (3, 'Dr. Iyer', 'Cardiology', 1000.00, 'NO_SHOW'), (4, 'Dr. Iyer', 'Cardiology', 1000.00, 'DONE'), (5, 'Dr. Khan', 'Orthopaedics', 600.00, 'DONE'), (6, 'Dr. Khan', 'Orthopaedics', NULL, 'DONE'), (7, 'Dr. Bose', 'Orthopaedics', 700.00, 'NO_SHOW'), (8, 'Dr. Mehta', 'Paediatrics', 500.00, 'DONE'), (9, 'Dr. Mehta', 'Paediatrics', NULL, 'DONE'), (10, 'Dr. Mehta', 'Paediatrics', 500.00, 'DONE'), (11, 'Dr. Sen', 'Dermatology', 900.00, 'NO_SHOW'), (12, 'Dr. Sen', 'Dermatology', 900.00, 'NO_SHOW');
SELECT department, COUNT(*) AS visits, COUNT(fee) AS paid_visits, ROUND(AVG(fee), 2) AS avg_paid_fee, ROUND(AVG(COALESCE(fee, 0)), 2) AS avg_fee_per_visitFROM opd_visitsWHERE status = 'DONE'GROUP BY departmentORDER BY department;+--------------+--------+-------------+--------------+-------------------+ | department | visits | paid_visits | avg_paid_fee | avg_fee_per_visit | +--------------+--------+-------------+--------------+-------------------+ | Cardiology | 3 | 3 | 866.67 | 866.67 | | Orthopaedics | 2 | 1 | 600.00 | 300.00 | | Paediatrics | 3 | 2 | 500.00 | 333.33 | +--------------+--------+-------------+--------------+-------------------+
What this code does
- Paediatrics had 3 completed visits, but one was free (
NULLfee), soCOUNT(fee)is 2 andAVG(fee)is 500.00: the average of the paid visits only. AVG(COALESCE(fee, 0))counts the free visit as 0 and gives 333.33, the average income per visit. Both numbers are correct; they answer different questions.
COUNT(column) skips NULLs, AVG divides by the non-NULL count only, and SUM of no rows is NULL. Use COUNT(*) for rows, AVG(COALESCE(x, 0)) when missing means zero, and COALESCE(SUM(x), 0) when an empty total must print as 0.
Predict the output
Counts and averages with NULL
Read the code and pick what it prints.
Round 1 of 2
Course 5 has marks 80 and NULL.
Mistake 4: counting the wrong thing
In order_items a row is one line of an order, not one order. The query below counts the lines and the orders, and averages per line and per order.
Example 4 · Order lines versus orders
SELECT COUNT(*) AS order_lines, COUNT(DISTINCT order_id) AS orders, ROUND(AVG(quantity * unit_price), 2) AS avg_line_value, ROUND(SUM(quantity * unit_price) / COUNT(DISTINCT order_id), 2) AS avg_order_valueFROM order_items;+-------------+--------+----------------+-----------------+ | order_lines | orders | avg_line_value | avg_order_value | +-------------+--------+----------------+-----------------+ | 25 | 15 | 12373.72 | 20622.87 | +-------------+--------+----------------+-----------------+
What this code does
order_itemshas 25 lines from 15 orders. A report that says "25 orders" has counted lines.- The average line is smaller than the average order, because many orders have two or three lines. Averaging the wrong unit gives a believable but wrong number.
COUNT(DISTINCT order_id) gives orders, and the average order value is SUM(quantity * unit_price) / COUNT(DISTINCT order_id). Before you write an aggregate, say out loud what one row of the table is.
Module quiz
Questions from the whole module. Your first answer is your score.
Problems
Graded easy to hard, using everything from this module.
Try each one yourself first. Use the hint if you get stuck. Mark it as done when it works.
Exercise 1· Easy
For each city, show the number of customers and how many have a phone number (city, customers, with_phone), ordered by city.
+-----------+-----------+------------+ | city | customers | with_phone | +-----------+-----------+------------+ | Bengaluru | 1 | 1 | | Chennai | 2 | 1 | | Delhi | 2 | 2 | | Hyderabad | 1 | 0 | | Kochi | 1 | 1 | | Mumbai | 1 | 0 | | Pune | 2 | 2 | +-----------+-----------+------------+
Exercise 2· Easy
For each course, show the lowest and highest marks (course_id, lowest, highest), ordered by course_id.
+-----------+--------+---------+ | course_id | lowest | highest | +-----------+--------+---------+ | 1 | 64 | 95 | | 2 | 38 | 92 | | 3 | 67 | 72 | | 4 | 45 | 81 | | 5 | 80 | 80 | +-----------+--------+---------+
Exercise 3· Medium
Predict, then check: for each dept_id, show the number of employees and how many have a manager (people, with_manager). Order by dept_id. Which department has fewer managers than people, and why?
+---------+--------+--------------+ | dept_id | people | with_manager | +---------+--------+--------------+ | NULL | 1 | 1 | | 1 | 5 | 4 | | 2 | 3 | 3 | | 3 | 2 | 2 | | 4 | 2 | 2 | +---------+--------+--------------+
Exercise 4· Medium
This query fails with error 1055. Fix it so it shows each city with its number of customers and their names A to Z, separated by , (city, customers, names), ordered by city.
SELECT city, name, COUNT(*) AS customersFROM customersGROUP BY city;+-----------+-----------+---------------------------+ | city | customers | names | +-----------+-----------+---------------------------+ | Bengaluru | 1 | Asha Rao | | Chennai | 2 | Meena Iyer, Ravi Kumar | | Delhi | 2 | Kabir Singh, Rahul Verma | | Hyderabad | 1 | Imran Khan | | Kochi | 1 | Divya Nair | | Mumbai | 1 | Arjun Mehta | | Pune | 2 | Priya Sharma, Sneha Patil | +-----------+-----------+---------------------------+
Exercise 5· Medium
Show the months ('%Y-%m') with more than 2 orders that were not cancelled, with the number of such orders and of different customers (month, orders, customers), in month order.
+---------+--------+-----------+ | month | orders | customers | +---------+--------+-----------+ | 2025-03 | 3 | 3 | | 2025-04 | 3 | 3 | +---------+--------+-----------+
Exercise 6· Medium
For each student with at least 2 courses, show the number of courses, the average of the marks given rounded to 1 decimal, and how many marks are still pending (student_id, courses, avg_marks, pending), ordered by student_id.
+------------+---------+-----------+---------+ | student_id | courses | avg_marks | pending | +------------+---------+-----------+---------+ | 1 | 3 | 85.0 | 0 | | 2 | 2 | 82.0 | 0 | | 3 | 2 | 62.5 | 0 | | 4 | 3 | 88.7 | 0 | | 5 | 2 | 45.0 | 1 | | 6 | 2 | 76.5 | 0 | | 7 | 2 | 51.0 | 0 | +------------+---------+-----------+---------+
Exercise 7· Hard
Which products appear in at least 2 different orders? Show product_id, the number of orders (orders) and the total units (units), most orders first, then by product_id.
+------------+--------+-------+ | product_id | orders | units | +------------+--------+-------+ | 6 | 4 | 7 | | 1 | 2 | 2 | | 4 | 2 | 2 | | 7 | 2 | 3 | | 9 | 2 | 2 | | 10 | 2 | 3 | | 11 | 2 | 3 | | 13 | 2 | 3 | +------------+--------+-------+
Exercise 8· Hard
Company report for employees who have a department: per dept_id, the headcount, the salary bill, how many earn Rs 1,00,000 or more (high_earners, with SUM(CASE ...)), and a total row labelled All departments (department, headcount, salary_bill, high_earners). Detail rows by dept_id, total last.
+-----------------+-----------+-------------+--------------+ | department | headcount | salary_bill | high_earners | +-----------------+-----------+-------------+--------------+ | 1 | 5 | 765000.00 | 4 | | 2 | 3 | 320000.00 | 1 | | 3 | 2 | 150000.00 | 0 | | 4 | 2 | 215000.00 | 1 | | All departments | 12 | 1450000.00 | 6 | +-----------------+-----------+-------------+--------------+
Interview questions
Questions interviewers ask on this topic.
Finish up
Mark complete & continue
Next: Mini project: ShopLite monthly sales report