GROUP BY one and several columns
45 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)GROUP BY splits the rows of a table into groups that share the same value, and then runs the aggregates once per group. One query gives the salary bill of every department, the customers in every city or the orders of every month.
One result row per group
GROUP BY puts all rows that share the same value in a column into one group. Each group becomes one row of the result, and every aggregate in SELECT is worked out separately for each group.
A per-department report should not need one query per department. GROUP BY dept_id finds the departments by itself: the first query gives headcount and salary bill for each, the second counts customers per city.
Example 1 · Headcount and salary bill per department
SELECT dept_id, COUNT(*) AS people, SUM(salary) AS salary_bill, ROUND(AVG(salary), 2) AS avg_salaryFROM employeesGROUP BY dept_idORDER BY dept_id;+---------+--------+-------------+------------+ | dept_id | people | salary_bill | avg_salary | +---------+--------+-------------+------------+ | NULL | 1 | 70000.00 | 70000.00 | | 1 | 5 | 765000.00 | 153000.00 | | 2 | 3 | 320000.00 | 106666.67 | | 3 | 2 | 150000.00 | 75000.00 | | 4 | 2 | 215000.00 | 107500.00 | +---------+--------+-------------+------------+
What this code does
- The 13 employees fall into 5 groups by
dept_id, so the result has 5 rows.COUNT,SUMandAVGare worked out inside each group. - Tanvi has no department, so she forms the
NULLgroup.ORDER BY dept_idlistsNULLfirst, because MySQL sortsNULLbefore every value. - Department 5 (Research) has no employees, so it has no row.
GROUP BYonly finds groups that have rows.
Example 2 · Customers per city, biggest first
SELECT city, COUNT(*) AS customersFROM customersGROUP BY cityORDER BY customers DESC, city;+-----------+-----------+ | city | customers | +-----------+-----------+ | Chennai | 2 | | Delhi | 2 | | Pune | 2 | | Bengaluru | 1 | | Hyderabad | 1 | | Kochi | 1 | | Mumbai | 1 | +-----------+-----------+
What this code does
- Each city becomes one row with its own count; 10 customers make 7 cities.
ORDER BY customers DESCputs the biggest cities first, andcitybreaks the ties in A to Z order, so the list is the same every time you run it.
Rows whose grouping column is NULL form one group of their own.
GROUP BY does not sort the result. Add ORDER BY whenever the order matters, which in a report is always.Grouping by several columns
GROUP BY a, b makes one group for each different combination of a and b. Two rows land in the same group only when both values match.
Students grouped by branch and year give one row for CSE year 2, one for CSE year 3, and so on.
SELECT branch, year, COUNT(*) AS studentsFROM studentsGROUP BY branch, yearORDER BY branch, year;+--------+------+----------+ | branch | year | students | +--------+------+----------+ | CSE | 2 | 2 | | CSE | 3 | 1 | | ECE | 2 | 1 | | ECE | 3 | 1 | | IT | 1 | 1 | | IT | 4 | 1 | | MECH | 1 | 1 | +--------+------+----------+
A combination that has no rows, such as MECH year 4, does not appear at all. The order of the columns in GROUP BY does not change the groups; use ORDER BY to decide how the rows are listed.
Example 3 · Store orders per city and category
DROP TABLE IF EXISTS store_orders;CREATE TABLE store_orders ( order_id INT PRIMARY KEY, city VARCHAR(20) NOT NULL, category VARCHAR(30) NOT NULL, order_date DATE NOT NULL, amount DECIMAL(8,2) NOT NULL);
INSERT INTO store_orders VALUES (1, 'Bengaluru', 'Fashion', '2025-03-02', 420.00), (2, 'Bengaluru', 'Fashion', '2025-03-15', 380.00), (3, 'Bengaluru', 'Toys', '2025-03-20', 150.00), (4, 'Bengaluru', 'Toys', '2025-04-03', 210.00), (5, 'Chennai', 'Books', '2025-03-05', 260.00), (6, 'Chennai', 'Books', '2025-04-11', 310.00), (7, 'Chennai', 'Accessories', '2025-04-18', 540.00), (8, 'Hyderabad', 'Fashion', '2025-03-09', 650.00), (9, 'Hyderabad', 'Fashion', '2025-04-22', 480.00), (10, 'Hyderabad', 'Fashion', '2025-05-01', 520.00), (11, 'Bengaluru', 'Fashion', '2025-05-06', 450.00), (12, 'Chennai', 'Accessories', '2025-05-14', 300.00);
SELECT city, category, COUNT(*) AS orders, SUM(amount) AS revenueFROM store_ordersGROUP BY city, categoryORDER BY city, category;+-----------+-------------+--------+---------+ | city | category | orders | revenue | +-----------+-------------+--------+---------+ | Bengaluru | Fashion | 3 | 1250.00 | | Bengaluru | Toys | 2 | 360.00 | | Chennai | Accessories | 2 | 840.00 | | Chennai | Books | 2 | 570.00 | | Hyderabad | Fashion | 3 | 1650.00 | +-----------+-------------+--------+---------+
What this code does
- Each (city, category) pair is one group: Bengaluru sells two categories, so it gets two rows.
- Grouping by
cityalone would give 3 rows and mix both Bengaluru categories into one total. The second column splits each city further. ORDER BY city, categorylists the pairs in a fixed, readable order.
ONLY_FULL_GROUP_BY: every selected column grouped or aggregated
ONLY_FULL_GROUP_BY is a MySQL rule, on by default since MySQL 5.7.5: every column in SELECT must be in GROUP BY or inside an aggregate. A plain column with many values in one group breaks it with error 1055.
Ask for a product name next to COUNT(*) per category and MySQL refuses: the Mobiles group has three names, so which one should the row show? The first example shows the error, the second three correct fixes.
Example 4 · Error 1055: a column that is not grouped
-- Goal: each category, its products and how many there areSELECT category_id, name, COUNT(*) AS productsFROM productsGROUP BY category_id;ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'practice.products.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
What this code does
nameis the 2nd expression inSELECT. It is not inGROUP BYand not in an aggregate, so MySQL stops with error 1055 instead of picking one product name per category.- The message names your own database (here
practice), so your text may differ slightly. "Functionally dependent" means "has exactly one value per group"; the next example uses that idea.
Example 5 · Three correct fixes
-- Fix 1: summary values instead of the plain columnSELECT category_id, COUNT(*) AS products, MIN(price) AS cheapest, MAX(price) AS costliestFROM productsGROUP BY category_idORDER BY category_id;
-- Fix 2: all names in one cellSELECT category_id, GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS product_namesFROM productsGROUP BY category_idORDER BY category_id;
-- Fix 3: group by the primary key; then name has one value per groupSELECT product_id, name, SUM(stock) AS stockFROM productsWHERE category_id = 4GROUP BY product_idORDER BY product_id;+-------------+----------+----------+-----------+ | category_id | products | cheapest | costliest | +-------------+----------+----------+-----------+ | NULL | 1 | 500.00 | 500.00 | | 2 | 3 | 17999.00 | 69900.00 | | 3 | 2 | 42990.00 | 64990.00 | | 4 | 3 | 849.00 | 2199.00 | | 5 | 3 | 399.00 | 650.00 | | 6 | 2 | 899.00 | 2499.00 | | 7 | 1 | 299.00 | 299.00 | +-------------+----------+----------+-----------+ +-------------+----------------------------------------------------------------+ | category_id | product_names | +-------------+----------------------------------------------------------------+ | NULL | Gift Card Rs. 500 | | 2 | iPhone 15, Redmi Note 13, Samsung Galaxy M35 | | 3 | HP Victus Gaming, Lenovo IdeaPad Slim 3 | | 4 | boAt Airdopes 141, Logitech M331 Mouse, Mi Power Bank 20000mAh | | 5 | Atomic Habits, Clean Code, Hands-On Python | | 6 | Cotton Kurta, Running Shoes | | 7 | Rubik's Cube | +-------------+----------------------------------------------------------------+ +------------+------------------------+-------+ | product_id | name | stock | +------------+------------------------+-------+ | 6 | boAt Airdopes 141 | 120 | | 7 | Mi Power Bank 20000mAh | 60 | | 8 | Logitech M331 Mouse | 0 | +------------+------------------------+-------+
What this code does
- Fix 1 asks for values that make sense for a group: the count, the cheapest and the costliest price.
- Fix 2 uses
GROUP_CONCAT, a MySQL aggregate that joins a group's values into one text. By default it is cut at 1024 bytes (thegroup_concat_max_lensetting), so keep it for short lists. - Fix 3 works because
product_idis the primary key: each group has one row, sonameis functionally dependent on it. With joins (next module), grouping by a key such ascustomer_idlets you also show that customer's name.
The fix depends on what you meant:
- You want one row per name too → add it to
GROUP BY(this changes the groups). - You want one summary value → wrap it:
MIN(name),MAX(price),COUNT(name). - You want all the values in one cell →
GROUP_CONCAT(name ORDER BY name SEPARATOR ', '). - Any one value is truly fine →
ANY_VALUE(name), which says so on purpose.
Fix the error
Fix the grouped query
Read the error, then pick the line that fixes it.
Round 1 of 2
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.products.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
ONLY_FULL_GROUP_BY off. The error is pointing at a real question your query has not answered.Grouping by an expression such as the month
GROUP BY can use an expression, not only a column. GROUP BY DATE_FORMAT(order_date, '%Y-%m') puts all orders of one month together, and a CASE expression can put salaries into bands.
Most time reports group by month, year or weekday: the rows store exact dates, and an expression turns each one into the bucket to report on. The query below groups salaries into three bands.
SELECT CASE WHEN salary >= 150000 THEN 'Senior' WHEN salary >= 90000 THEN 'Mid' ELSE 'Junior' END AS band, COUNT(*) AS people, MIN(salary) AS from_salaryFROM employeesGROUP BY bandORDER BY from_salary;+--------+--------+-------------+ | band | people | from_salary | +--------+--------+-------------+ | Junior | 5 | 60000.00 | | Mid | 5 | 90000.00 | | Senior | 3 | 150000.00 | +--------+--------+-------------+
MySQL lets you write the expression once in SELECT with an alias and then GROUP BY that alias. Group months by year and month: MONTH(order_date) alone would put January 2025 and January 2026 in the same group.
Example 6 · Orders per month
SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, COUNT(*) AS ordersFROM ordersGROUP BY monthORDER BY month;
SELECT YEAR(order_date) AS yr, MONTH(order_date) AS mon, COUNT(*) AS ordersFROM ordersGROUP BY YEAR(order_date), MONTH(order_date)ORDER BY yr, mon;+---------+--------+ | month | orders | +---------+--------+ | 2025-01 | 2 | | 2025-02 | 3 | | 2025-03 | 3 | | 2025-04 | 3 | | 2025-05 | 3 | | 2025-06 | 1 | +---------+--------+ +------+------+--------+ | yr | mon | orders | +------+------+--------+ | 2025 | 1 | 2 | | 2025 | 2 | 3 | | 2025 | 3 | 3 | | 2025 | 4 | 3 | | 2025 | 5 | 3 | | 2025 | 6 | 1 | +------+------+--------+
What this code does
DATE_FORMAT(order_date, '%Y-%m')turns 2025-03-15 into the text2025-03, so every March order lands in one group. Text like2025-03also sorts in date order.- The second query keeps year and month as two numbers. Both columns are needed:
MONTHalone would merge the same month of different years. GROUP BY monthuses the alias fromSELECT. MySQL allows this; the lesson on SELECT order explains where aliases work and where they do not.
Next step
Check your understanding
12 quick questions