SQL Aggregate Functions, GROUP BY and HAVING
Before MySQL 5.7.5, the query SELECT shop, product, SUM(lbs) FROM sales GROUP BY shop; ran fine and returned, for each store, the product from some row of the group — which row, the server never promised. In MySQL 5.7.5 and later the same query fails with ERROR 1055: the Acme group has three products, and the server no longer picks one of them at random.
An aggregate function (COUNT, SUM, AVG, MIN, MAX) computes a single value from a set of rows. GROUP BY splits the rows into groups that share the values of the listed columns, and aggregate functions are then computed separately for each group. HAVING filters groups by the result of an aggregate, the same way WHERE filters individual rows.
The sales Practice Table
All examples in this lesson run on the sales table from the lesson on the SELECT statement: eight fruit sales in three grocery stores, with quantities in pounds and prices per pound in US dollars; two items have no price yet (NULL). If you do not have the table, create it with this script. The syntax is MySQL 8.
CREATE TABLE sales (
id INT NOT NULL AUTO_INCREMENT,
shop VARCHAR(50) NOT NULL,
product VARCHAR(50) NOT NULL,
lbs INT NOT NULL,
price DECIMAL(6,2) NULL,
PRIMARY KEY (id)
);
INSERT INTO sales (shop, product, lbs, price) VALUES
('Acme', 'apples', 10, 1.50),
('Acme', 'pears', 14, 2.50),
('Acme', 'grapes', 20, NULL),
('Delta', 'apples', 20, 1.75),
('Delta', 'grapes', 34, 4.50),
('Delta', 'lemons', 34, NULL),
('Berry Hill', 'lemons', 14, 3.50),
('Berry Hill', 'apples', 54, 1.25); To check yourself: 200 pounds were sold in total — Acme 44, Delta 88, Berry Hill 68; six of the eight rows have a price.
Aggregate Functions: COUNT, SUM, AVG, MIN, MAX
Aggregate functions perform a calculation on a set of values and return a single value. Without GROUP BY, all rows that pass WHERE form one set, and the query returns exactly one row.
| Function | What it computes | NULL in the column | On an empty set |
|---|---|---|---|
COUNT(*) | Counts the rows that satisfy the query condition | Rows with NULL are counted | 0 |
COUNT(column) | Counts the column values that are not NULL | Skipped | 0 |
SUM | Computes the arithmetic sum of all values in the column | Skipped | NULL |
AVG | Computes the arithmetic mean of all values | Skipped and not counted in the divisor | NULL |
MAX | Finds the largest of all selected values | Skipped | NULL |
MIN | Finds the smallest of all selected values | Skipped | NULL |
Aggregate functions, except COUNT(*), ignore NULL values. COUNT(column) skips NULL too — more on that in the section on COUNT.
Using aggregate functions (users is the user table of the forum practice database; its script is in The Forum Practice Database):
SELECT count(DISTINCT username) FROM users; -- number of distinct values in the username column
SELECT MAX(id) FROM users;
SELECT MIN(id) FROM users; All the functions at once on the sales table:
SELECT COUNT(*) AS all_rows,
COUNT(price) AS priced,
COUNT(DISTINCT product) AS products,
SUM(lbs) AS total_lbs,
AVG(price) AS avg_price,
MIN(price) AS min_price,
MAX(price) AS max_price
FROM sales; +----------+--------+----------+-----------+-----------+-----------+-----------+
| all_rows | priced | products | total_lbs | avg_price | min_price | max_price |
+----------+--------+----------+-----------+-----------+-----------+-----------+
| 8 | 6 | 4 | 200 | 2.500000 | 1.25 | 4.50 |
+----------+--------+----------+-----------+-----------+-----------+-----------+ avg_price is the price total of 15.00 divided by the 6 rows that have a price, not by all 8 (that would be 1.875). If a missing price should count as zero, write AVG(COALESCE(price, 0)).
And what do the functions return when no row matches the condition?
SELECT COUNT(*), SUM(lbs) FROM sales WHERE shop = 'Northgate'; +----------+----------+
| COUNT(*) | SUM(lbs) |
+----------+----------+
| 0 | NULL |
+----------+----------+ SUM returned NULL, not 0. In Java, rs.getInt(2) returns 0 for such a value, and only rs.wasNull() or COALESCE(SUM(lbs), 0) in the query itself lets you tell “no sales” from “sold 0 pounds”.
COUNT(*), COUNT(column) and COUNT(DISTINCT)
COUNT comes in three forms: COUNT(*) counts rows, COUNT(column) counts the values of that column that are not NULL, and COUNT(DISTINCT column) counts the different non-NULL values. For the price column of sales that gives 8, 6 and 6: all six known prices are different.
SELECT COUNT(DISTINCT shop) AS shops,
COUNT(DISTINCT product) AS products,
COUNT(DISTINCT shop, product) AS pairs
FROM sales; +-------+----------+-------+
| shops | products | pairs |
+-------+----------+-------+
| 3 | 4 | 8 |
+-------+----------+-------+ COUNT(DISTINCT shop, product) counts distinct combinations of values. Several columns inside COUNT(DISTINCT ...) are a MySQL extension, and rows where at least one of those columns is NULL are left out of the count. In PostgreSQL the same thing is written as COUNT(DISTINCT (shop, product)).
What Will This Query Return: COUNT with a Condition
You need the number of items priced above $2. The first idea is to pass the condition to COUNT:
SELECT COUNT(price > 2) AS attempt,
SUM(price > 2) AS expensive,
COUNT(CASE WHEN price > 2 THEN 1 END) AS expensive_std
FROM sales; +---------+-----------+---------------+
| attempt | expensive | expensive_std |
+---------+-----------+---------------+
| 6 | 3 | 3 |
+---------+-----------+---------------+ Only three items cost more than $2 — Acme’s pears, Delta’s grapes and Berry Hill’s lemons — yet COUNT(price > 2) returned 6. In MySQL a comparison evaluates to 1 or 0 (or NULL when there is no price), and COUNT counts everything that is not NULL, zeros included. SUM(price > 2) adds up the ones and works, but only in MySQL. The portable version is COUNT(CASE WHEN condition THEN 1 END): when the condition is false, a CASE without ELSE returns NULL, and the row is not counted.
COUNT in Java is a long, not an int
In MySQL, COUNT returns a BIGINT, so read it with rs.getLong(1). Connector/J returns it from getObject() as a Long, and the cast (Integer) rs.getObject(1) throws a ClassCastException. SUM and AVG over an integer column return DECIMAL, which arrives in Java as a BigDecimal.
The GROUP BY Clause
The GROUP BY clause combines the rows of a result by one or more columns. Its syntax is GROUP BY column_name. Aggregate functions in such a query are computed separately for each group, and the result has one row per group.
Suppose there is a table with the number of pounds of each kind of fruit a store sold (it is the sales table from the first section without the price column):
| id | shop | product | lbs |
|---|---|---|---|
| 1 | Acme | apples | 10 |
| 2 | Acme | pears | 14 |
| 3 | Acme | grapes | 20 |
| 4 | Delta | apples | 20 |
| 5 | Delta | grapes | 34 |
| 6 | Delta | lemons | 34 |
| 7 | Berry Hill | lemons | 14 |
| 8 | Berry Hill | apples | 54 |
Using GROUP BY, find the number of pounds sold by each store:
SELECT shop, SUM(lbs) AS total_lbs
FROM sales GROUP BY shop; | shop | total_lbs |
|---|---|
| Acme | 44 |
| Delta | 88 |
| Berry Hill | 68 |
Check against the table: Acme 10 + 14 + 20 = 44, Delta 20 + 34 + 34 = 88, Berry Hill 14 + 54 = 68, 200 in total — the same as SUM(lbs) over the whole table. You can group by several columns (GROUP BY shop, product), and rows with NULL in a grouping column form one shared group.
The order of groups in this result is not guaranteed. MySQL 5.7 sorted the result by the GROUP BY columns on its own; since MySQL 8.0 there is no implicit sorting. Groups usually come in order of appearance, as above, but code that relied on the old behavior may list them differently after an upgrade. If you need an order, add ORDER BY shop.
GROUP BY on Several Columns and Expressions
When you group by several columns, a group is a unique combination of their values. In sales each store–product pair occurs once, so GROUP BY shop, product returns 8 groups of one row each. A second level that splits a store into parts is more useful. For example, how many pounds each store sold with a known price and without one:
SELECT shop,
price IS NULL AS no_price,
COUNT(*) AS positions,
SUM(lbs) AS total_lbs
FROM sales
GROUP BY shop, price IS NULL
ORDER BY shop, no_price; +------------+----------+-----------+-----------+
| shop | no_price | positions | total_lbs |
+------------+----------+-----------+-----------+
| Acme | 0 | 2 | 24 |
| Acme | 1 | 1 | 20 |
| Berry Hill | 0 | 2 | 68 |
| Delta | 0 | 2 | 54 |
| Delta | 1 | 1 | 34 |
+------------+----------+-----------+-----------+ GROUP BY accepts expressions, not just columns: price IS NULL is 1 for rows without a price and 0 for the rest. Berry Hill has prices for everything, so it gets a single group. The five groups still add up to 200 pounds.
NULL in a Grouping Column
For GROUP BY, all NULLs are equal to each other and land in one group — unlike WHERE, where NULL = NULL is not true:
SELECT price, COUNT(*) AS positions, SUM(lbs) AS total_lbs
FROM sales
WHERE price IS NULL OR price < 1.75
GROUP BY price
ORDER BY price; +-------+-----------+-----------+
| price | positions | total_lbs |
+-------+-----------+-----------+
| NULL | 2 | 54 |
| 1.25 | 1 | 54 |
| 1.50 | 1 | 10 |
+-------+-----------+-----------+ Rows 3 and 6 (Acme’s grapes and Delta’s lemons, 20 + 34 pounds) ended up in the price = NULL group. In ascending order MySQL puts that group first.
A Grand Total Row: WITH ROLLUP
The WITH ROLLUP modifier adds a grand-total row after the groups; its grouping column holds NULL. The GROUPING(shop) function (MySQL 8.0.1+) returns 1 exactly for that total row, so you can label it:
SELECT IF(GROUPING(shop), 'Total', shop) AS shop_name,
SUM(lbs) AS total_lbs
FROM sales
GROUP BY shop WITH ROLLUP; +------------+-----------+
| shop_name | total_lbs |
+------------+-----------+
| Acme | 44 |
| Berry Hill | 68 |
| Delta | 88 |
| Total | 200 |
+------------+-----------+ COALESCE(shop, 'Total') instead of GROUPING() only works if the column itself has no NULLs: otherwise a regular NULL group gets the “Total” label too. Before MySQL 8.0.12, WITH ROLLUP could not be combined with ORDER BY. The WITH ROLLUP syntax exists only in MySQL and MariaDB; PostgreSQL, Oracle and SQL Server use the standard form GROUP BY ROLLUP(shop).
Which Columns You Can Select with GROUP BY: ONLY_FULL_GROUP_BY and Error 1055
In a query with GROUP BY, the SELECT list may contain only the GROUP BY columns and aggregate functions. Here is what happens if you add product to it:
SELECT shop, product, SUM(lbs) FROM sales GROUP BY shop; ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'forum.sales.product' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by The Acme group has three different products, and it is unclear which one to show. The ONLY_FULL_GROUP_BY mode has been on by default since MySQL 5.7.5; older versions ran such a query and returned the product from an arbitrary row of the group. There are three fixes: add the column to GROUP BY, wrap it in an aggregate function (MAX(product), GROUP_CONCAT(product)), or explicitly accept any value with ANY_VALUE(product).
If you need every product of the group, use GROUP_CONCAT — a MySQL aggregate function that joins the values of a group into one string:
SELECT shop,
GROUP_CONCAT(product ORDER BY product SEPARATOR ', ') AS products,
SUM(lbs) AS total_lbs
FROM sales
GROUP BY shop
ORDER BY shop; +------------+------------------------+-----------+
| shop | products | total_lbs |
+------------+------------------------+-----------+
| Acme | apples, grapes, pears | 44 |
| Berry Hill | apples, lemons | 68 |
| Delta | apples, grapes, lemons | 88 |
+------------+------------------------+-----------+ The GROUP_CONCAT result is cut to group_concat_max_len bytes (1024 by default) with a warning, not an error; for long lists, raise that variable. ERROR 1055 also does not occur when a column is uniquely determined by the grouping columns: with GROUP BY id on the primary key you can list any column of that table in SELECT, because MySQL detects the functional dependency.
The SQL HAVING Clause
The SQL HAVING clause sets a condition on the groups produced by GROUP BY: it checks the results of aggregate functions. WHERE selects rows before grouping and HAVING selects groups after it, so a condition like “the sum is greater than 60” can only go into HAVING.
Using SQL HAVING, list the stores that sold more than 60 pounds:
SELECT shop, SUM(lbs) AS total_lbs
FROM sales GROUP BY shop
HAVING SUM(lbs) > 60; | shop | total_lbs |
|---|---|
| Delta | 88 |
| Berry Hill | 68 |
MySQL also lets you use the alias in HAVING: HAVING total_lbs > 60. This is a MySQL extension; standard SQL and PostgreSQL require repeating the function, as in the example above.
The aggregate in HAVING does not have to appear in the SELECT list: SELECT shop FROM sales GROUP BY shop HAVING COUNT(*) = 3; returns Acme and Delta — each of these stores has three items.
WHERE and HAVING in One Query
WHERE and HAVING differ in when they filter: WHERE discards rows before they get into groups, HAVING discards finished groups by the value of an aggregate. So a condition on a plain column goes into WHERE, and a condition on SUM, COUNT or another aggregate can only go into HAVING.
WHERE and HAVING are often needed in the same query. How many pounds each store sold, not counting apples — and only the stores where those sales exceed 30 pounds:
SELECT shop, SUM(lbs) AS total_lbs
FROM sales
WHERE product <> 'apples'
GROUP BY shop
HAVING SUM(lbs) > 30
ORDER BY shop; +-------+-----------+
| shop | total_lbs |
+-------+-----------+
| Acme | 34 |
| Delta | 68 |
+-------+-----------+ WHERE removed the three apple rows, GROUP BY computed the sums (Acme 34, Delta 68, Berry Hill 14), and HAVING dropped Berry Hill.
| Aspect | WHERE | HAVING |
|---|---|---|
| What it filters | Individual table rows | Groups produced by GROUP BY |
| When it runs | Before grouping | After grouping |
| Aggregate functions in the condition | Not allowed: ERROR 1111 (HY000): Invalid use of group function | Allowed |
Aliases from SELECT | Not allowed: ERROR 1054 | Allowed in MySQL, not in standard SQL |
| Performance | Conditions on plain columns belong here: fewer rows reach grouping, and an index can be used | Only conditions on aggregates |
GROUP BY and HAVING in the Query Execution Order
Logically, a query runs like this: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Groups appear only at the third step, so an aggregate function in WHERE is not allowed, while ORDER BY, which comes after SELECT, can sort by an aggregate and by its alias.
SELECT shop, SUM(lbs) FROM sales WHERE SUM(lbs) > 60 GROUP BY shop; ERROR 1111 (HY000): Invalid use of group function Sorting groups by an aggregate — the best-selling products:
SELECT product, COUNT(*) AS shops, SUM(lbs) AS total_lbs
FROM sales
GROUP BY product
ORDER BY total_lbs DESC; +---------+-------+-----------+
| product | shops | total_lbs |
+---------+-------+-----------+
| apples | 3 | 84 |
| grapes | 2 | 54 |
| lemons | 2 | 48 |
| pears | 1 | 14 |
+---------+-------+-----------+ With LIMIT 1 the same query returns the leader — apples, 84 pounds. The full table of steps, with what is available at each one, and the breakdown of the alias-in-WHERE error are in the lesson on the SELECT statement. Grouping data from several tables (for example, the number of posts per forum user) is built on top of a JOIN: the tables are combined at the FROM step, and GROUP BY works on the combined rows.
Where GROUP BY Queries Go Wrong
Mistakes in queries with aggregates come in two kinds: MySQL rejects some of them with an error code, while others run and return a plausible but wrong number. The second kind is more dangerous — those rows are highlighted in the table.
| Situation | What happens | How to do it right |
|---|---|---|
An aggregate function in WHERE | ERROR 1111: Invalid use of group function | Put the condition in HAVING |
A column outside GROUP BY in the SELECT list | ERROR 1055 (only_full_group_by); before MySQL 5.7.5 — a value from a random row | Add it to GROUP BY or wrap it in an aggregate / ANY_VALUE() |
Relying on the row order after GROUP BY | Since MySQL 8.0 groups are not sorted | An explicit ORDER BY |
AVG(price) with missing prices | Rows with NULL are in neither the sum nor the divisor | AVG(COALESCE(price, 0)) if a missing value means zero |
COUNT(price > 2) to count by a condition | Every row with a price is counted: 6 instead of 3 | COUNT(CASE WHEN price > 2 THEN 1 END), or SUM(price > 2) in MySQL |
COUNT(price) when you need the number of rows | Rows with NULL are skipped: 6 instead of 8 | COUNT(*) |
SUM on an empty set | NULL; in Java getInt() silently returns 0 | COALESCE(SUM(lbs), 0) or rs.wasNull() |
A condition on a plain column in HAVING | MySQL runs it, but the rows are grouped first and discarded afterwards; no index is used | Move the condition to WHERE |
Frequently Asked Questions
Can you use HAVING without GROUP BY?
Yes. Without GROUP BY the whole result is one group, and HAVING decides whether to return it: SELECT SUM(lbs) FROM sales HAVING SUM(lbs) > 100; returns one row with 200, and with > 500 the result is empty. MySQL also accepts conditions on plain columns in HAVING, but they belong in WHERE. The difference is covered in the section WHERE and HAVING.
What is the difference between COUNT(*), COUNT(1) and COUNT(id)?
COUNT(*) and COUNT(1) are equivalent in MySQL: both count rows, and the optimizer executes them the same way, so COUNT(1) is not faster. COUNT(column) counts only non-NULL values; for a NOT NULL column such as id the result equals COUNT(*), while for price in the practice table it is 6 instead of 8. The other forms are in the section on COUNT.
How do I find duplicates in a table with GROUP BY?
Group by the columns that are supposed to be unique and keep the groups with more than one row: SELECT product, COUNT(*) FROM sales GROUP BY product HAVING COUNT(*) > 1; returns apples (3), grapes (2) and lemons (2). To see the duplicate rows themselves, add GROUP_CONCAT(id) — you get a list of their ids.
How do I get the row with the maximum value in each group?
MAX(lbs) with GROUP BY shop returns only the number, not the product. You need a subquery: SELECT shop, product, lbs FROM sales WHERE (shop, lbs) IN (SELECT shop, MAX(lbs) FROM sales GROUP BY shop); returns Acme’s grapes (20), Berry Hill’s apples (54) and two Delta rows — grapes and lemons at 34 pounds each, because they share the maximum. In MySQL 8.0+ the same task is solved with the window function ROW_NUMBER() OVER (PARTITION BY shop ORDER BY lbs DESC), which keeps exactly one row per store.
What is the difference between GROUP BY and DISTINCT?
Without aggregate functions the result is the same: SELECT DISTINCT shop FROM sales and SELECT shop FROM sales GROUP BY shop both return the three stores, and MySQL often executes the two queries the same way. The difference is in purpose: DISTINCT only removes duplicate result rows, while GROUP BY is there to compute something for each group and filter groups with HAVING. DISTINCT is covered in the lesson on SELECT.
Comments