Quiz: SQL Aggregate Functions, GROUP BY and HAVING
Study the lesson first: SQL Aggregate Functions, GROUP BY and HAVING
Total: 7 questions
1. How do aggregate functions work with GROUP BY, and how is HAVING different from WHERE?
How do aggregate functions work with GROUP BY, and how is HAVING different from WHERE?
The aggregate functions COUNT, SUM, AVG, MIN and MAX compute one value over a set of rows. They all skip NULL except COUNT(*), which counts whole rows (COUNT(1) is equivalent). On an empty set COUNT returns 0, while SUM, AVG, MIN and MAX return NULL.
Without GROUP BY the whole result is one group and one result row. GROUP BY collects rows with equal values into groups, and the functions are computed for each group separately:
SELECT shop, SUM(lbs) AS total_lbs
FROM sales
WHERE product <> 'apples' -- filters rows before grouping
GROUP BY shop
HAVING SUM(lbs) > 30 -- filters groups after grouping
ORDER BY shop;
- WHERE filters rows before grouping; aggregate functions are not allowed in it (
ERROR 1111: Invalid use of group function). Conditions on plain columns belong here: fewer rows reach grouping, and an index can be used. - HAVING filters groups after
GROUP BYand can use aggregates. WithoutGROUP BYit decides whether to return the single group. - With
GROUP BY, theSELECTlist may contain only grouping columns and aggregates. Otherwise, withONLY_FULL_GROUP_BY(on by default since MySQL 5.7.5), you getERROR 1055; fix it by adding the column toGROUP BYor wrapping it in an aggregate orANY_VALUE(). - Since MySQL 8.0,
GROUP BYno longer sorts the result implicitly — addORDER BY.
2. What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column), and what do aggregate functions return on an empty set?
What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column), and what do aggregate functions return on an empty set?
COUNT(*)counts rows, including rows withNULL;COUNT(1)is equivalent and not faster.COUNT(column)counts the non-NULLvalues: forpriceinsalesit is 6 of 8. For aNOT NULLcolumn such asid, the result equalsCOUNT(*).COUNT(DISTINCT column)counts the different non-NULLvalues:COUNT(DISTINCT shop)= 3,COUNT(DISTINCT product)= 4.COUNT(DISTINCT shop, product)= 8 — several columns inside are a MySQL extension; PostgreSQL writesCOUNT(DISTINCT (shop, product)).
When no row matches the condition, COUNT returns 0, while SUM, AVG, MIN and MAX return NULL:
SELECT COUNT(*), SUM(lbs) FROM sales WHERE shop = 'Northgate'; -- 0, NULL
In Java, read COUNT with rs.getLong(): MySQL returns a BIGINT, and (Integer) rs.getObject(1) throws a ClassCastException. rs.getInt() returns 0 for NULL, so you tell “no sales” from “sold 0 pounds” with rs.wasNull() or COALESCE(SUM(lbs), 0) in the query.
3. How does GROUP BY work on several columns and expressions, where do NULLs go, and in what order are groups returned?
How does GROUP BY work on several columns and expressions, where do NULLs go, and in what order are groups returned?
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 gives 8 groups of one row each. GROUP BY also accepts expressions:
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;
The result has 5 groups: Acme with a price (2 items, 24 pounds) and without one (1, 20), Berry Hill (2, 68), Delta with a price (2, 54) and without one (1, 34) — still 200 pounds in total.
- NULL: for
GROUP BY, allNULLs are equal and form one group (inWHERE,NULL = NULLis not true); in ascending order MySQL puts it first. - Group order: MySQL 5.7 sorted implicitly by the
GROUP BYcolumns; since MySQL 8.0 there is no such sorting — add an explicitORDER BY. - Sorting by an aggregate:
ORDER BYruns afterSELECT, so both the aggregate and its alias are available there:GROUP BY product ORDER BY total_lbs DESC LIMIT 1returns the leader — apples, 84 pounds.
4. How do you add a grand-total row to a GROUP BY result with WITH ROLLUP, and what is the GROUPING() function for?
How do you add a grand-total row to a GROUP BY result with WITH ROLLUP, and what is the GROUPING() function for?
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; -- Acme 44, Berry Hill 68, Delta 88, Total 200
COALESCE(shop, 'Total')instead ofGROUPING()only works if the column itself has noNULLs: otherwise a regular NULL group gets the “Total” label too.- Before MySQL 8.0.12,
WITH ROLLUPcould not be combined withORDER BY. - The
WITH ROLLUPsyntax exists only in MySQL and MariaDB; PostgreSQL, Oracle and SQL Server use the standard formGROUP BY ROLLUP(shop).
5. Which columns can you list in SELECT together with GROUP BY, and how do you fix error 1055 (ONLY_FULL_GROUP_BY)?
Which columns can you list in SELECT together with GROUP BY, and how do you fix error 1055 (ONLY_FULL_GROUP_BY)?
In a query with GROUP BY, the SELECT list may contain only the grouping columns and aggregate functions. The query SELECT shop, product, SUM(lbs) FROM sales GROUP BY shop; fails with ERROR 1055 ... incompatible with sql_mode=only_full_group_by: the Acme group has three products, and the server does not pick one at random. The mode has been on by default since MySQL 5.7.5; before that, such a query returned the product from an arbitrary row of the group.
Three ways to fix it:
- add the column to
GROUP BY; - wrap it in an aggregate:
MAX(product)orGROUP_CONCAT(product ORDER BY product SEPARATOR ', ')— all products of the group in one string (for Acme, “apples, grapes, pears”); - explicitly accept any value:
ANY_VALUE(product).
The GROUP_CONCAT result is cut to group_concat_max_len (1024 bytes by default) with a warning, not an error. ERROR 1055 also does not occur with a functional dependency: with GROUP BY id on the primary key you can list any column of that table in SELECT.
6. How do you get the row with the maximum value in each group if MAX with GROUP BY returns only the number?
How do you get the row with the maximum value in each group if MAX with GROUP BY returns only the number?
MAX(lbs) with GROUP BY shop returns only the number, and you cannot add product to SELECT — that gives ERROR 1055. You need a subquery that finds the maximum of each group, and then you select the rows by the “group — maximum” pair:
SELECT shop, product, lbs
FROM sales
WHERE (shop, lbs) IN (SELECT shop, MAX(lbs) FROM sales GROUP BY shop);
The result has four rows: Acme’s grapes (20), Berry Hill’s apples (54) and two Delta rows — grapes and lemons at 34 pounds each. If several rows in a group share the maximum, all of them are returned.
In MySQL 8.0+ the same task is solved with the window function ROW_NUMBER() OVER (PARTITION BY shop ORDER BY lbs DESC): it numbers the rows inside each store and lets you keep exactly one row per group.
7. What is the difference between GROUP BY and DISTINCT, how do you find duplicates with HAVING, and can you use HAVING without GROUP BY?
What is the difference between GROUP BY and DISTINCT, how do you find duplicates with HAVING, and can you use HAVING without GROUP BY?
- GROUP BY and DISTINCT. Without aggregate functions the result is the same:
SELECT DISTINCT shop FROM salesandSELECT shop FROM sales GROUP BY shopboth return the three stores, and MySQL often executes them the same way.DISTINCTonly removes duplicate result rows, whileGROUP BYis there to compute something for each group and filter groups withHAVING. - Duplicates. 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).GROUP_CONCAT(id)shows the ids of the duplicate rows. - An aggregate in HAVING does not have to be in
SELECT:SELECT shop FROM sales GROUP BY shop HAVING COUNT(*) = 3;returns Acme and Delta. - HAVING without GROUP BY. The whole result is one group, and
HAVINGdecides whether to return it:SELECT SUM(lbs) FROM sales HAVING SUM(lbs) > 100;returns 200, and with> 500the result is empty. Conditions on plain columns go intoWHERE.