Quiz: 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?

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 BY and can use aggregates. Without GROUP BY it decides whether to return the single group.
  • With GROUP BY, the SELECT list may contain only grouping columns and aggregates. Otherwise, with ONLY_FULL_GROUP_BY (on by default since MySQL 5.7.5), you get ERROR 1055; fix it by adding the column to GROUP BY or wrapping it in an aggregate or ANY_VALUE().
  • Since MySQL 8.0, GROUP BY no longer sorts the result implicitly — add ORDER BY.
2. 

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 with NULL; COUNT(1) is equivalent and not faster.
  • COUNT(column) counts the non-NULL values: for price in sales it is 6 of 8. For a NOT NULL column such as id, the result equals COUNT(*).
  • COUNT(DISTINCT column) counts the different non-NULL values: COUNT(DISTINCT shop) = 3, COUNT(DISTINCT product) = 4. COUNT(DISTINCT shop, product) = 8 — several columns inside are a MySQL extension; PostgreSQL writes COUNT(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?

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, all NULLs are equal and form one group (in WHERE, NULL = NULL is not true); in ascending order MySQL puts it first.
  • Group order: MySQL 5.7 sorted implicitly by the GROUP BY columns; since MySQL 8.0 there is no such sorting — add an explicit ORDER BY.
  • Sorting by an aggregate: ORDER BY runs after SELECT, so both the aggregate and its alias are available there: GROUP BY product ORDER BY total_lbs DESC LIMIT 1 returns 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?

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 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).
5. 

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) or GROUP_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?

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?

  • 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 them the same way. DISTINCT only removes duplicate result rows, while GROUP BY is there to compute something for each group and filter groups with HAVING.
  • 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 HAVING decides whether to return it: SELECT SUM(lbs) FROM sales HAVING SUM(lbs) > 100; returns 200, and with > 500 the result is empty. Conditions on plain columns go into WHERE.
Page 1 of 1