Practical 4: Aggregate Functions, GROUP BY and HAVING

Chapter Forty

Syllabus topic Module 2, Practical 4: "Simple Queries with Aggregate functions"

Pages 149 to 152 of 206

Aim

To write queries using aggregate functions.

What an aggregate function is

An ordinary expression works on one row at a time. An aggregate function works on many rows at once and returns a single value: how many, the smallest, the largest, the total, the average.

There are five, and MU's wording points at all of them.

Function	Returns
`COUNT(x)`	how many
`SUM(x)`	the total
`AVG(x)`	the average
`MIN(x)`	the smallest
`MAX(x)`	the largest

SELECT COUNT(*) AS books,
       MIN(price) AS cheapest,
       MAX(price) AS dearest,
       SUM(price) AS total,
       ROUND(AVG(price), 2) AS average
FROM book;

+-------+----------+---------+---------+---------+
| books | cheapest | dearest | total   | average |
+-------+----------+---------+---------+---------+
|     8 |   280.00 |  720.50 | 4130.50 |  516.31 |
+-------+----------+---------+---------+---------+

One row out of eight rows in. That is what an aggregate does: it collapses many rows into one.

ROUND(AVG(price), 2) is there because AVG returns as many decimal places as it needs, and a price wants two. ROUND is in [Practical 5: Math Functions].

The one rule about NULL, and the one exception

Every aggregate except COUNT(*) ignores NULLs.

SELECT COUNT(*)           AS all_loans,
       COUNT(loan_id)     AS loans_with_an_id,
       COUNT(returned_on) AS loans_returned
FROM loan;

+-----------+------------------+----------------+
| all_loans | loans_with_an_id | loans_returned |
+-----------+------------------+----------------+
|         7 |                7 |              5 |
+-----------+------------------+----------------+

Seven loans, seven loan ids, and only five return dates, because two books are still out and their returned_on is NULL.

Written	Counts
`COUNT(*)`	rows, whatever is in them
`COUNT(column)`	rows where that column is not NULL
`COUNT(DISTINCT column)`	different non-NULL values

That table answers "what is the difference between COUNT(*) and COUNT(column)", which is asked in this practical's viva more than any other question.

The same rule bites hardest on AVG, and this is worth being careful about:

SELECT SUM(returned_on IS NOT NULL) AS returned,
       COUNT(*) AS rows_in_table,
       AVG(DATEDIFF(returned_on, issued_on)) AS avg_days_out
FROM loan;

+----------+---------------+--------------+
| returned | rows_in_table | avg_days_out |
+----------+---------------+--------------+
|        5 |             7 |      14.2000 |
+----------+---------------+--------------+

AVG divided by five, the loans that have come back, not by seven. That is almost always what you want, and it is almost never what a student expects. An average over a column with NULLs in it is an average of the rows that have values.

GROUP BY

An aggregate over the whole table gives one number. GROUP BY gives one number per group.

SELECT subject, COUNT(*) AS how_many
FROM book
GROUP BY subject
ORDER BY how_many DESC, subject;

+-------------+----------+
| subject     | how_many |
+-------------+----------+
| Databases   |        3 |
| Programming |        2 |
| Mathematics |        1 |
| Networks    |        1 |
| Systems     |        1 |
+-------------+----------+

The server sorted the rows into piles by subject and counted each pile.

SELECT subject,
       COUNT(*) AS how_many,
       MIN(price) AS cheapest,
       ROUND(AVG(price), 2) AS average
FROM book
GROUP BY subject
ORDER BY subject;

Practical 4: Aggregate Functions, GROUP BY and HAVING

+-------------+----------+----------+---------+
| subject     | how_many | cheapest | average |
+-------------+----------+----------+---------+
| Databases   |        3 |   280.00 |  499.83 |
| Mathematics |        1 |   430.00 |  430.00 |
| Networks    |        1 |   540.75 |  540.75 |
| Programming |        2 |   395.00 |  502.50 |
| Systems     |        1 |   655.25 |  655.25 |
+-------------+----------+----------+---------+

The rule about what may go in the SELECT

Every column in the SELECT must either be in the GROUP BY or be inside an aggregate function. Anything else has no single answer per group.

SELECT subject, title, COUNT(*) FROM book GROUP BY subject;

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'librarydb.book.title' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Error 1055. Three books are Databases; which of their three titles should the one Databases row show? There is no answer, so the server refuses.

That refusal comes from a setting called ONLY_FULL_GROUP_BY, which has been on by default since MySQL 5.7. On an older server, or one where it has been switched off, the query runs and silently picks a title at random. If your laboratory's server accepts the query above, it is not enforcing the rule, and the answer you get is not to be trusted.

HAVING

WHERE filters rows, before grouping. HAVING filters groups, after.

SELECT subject, COUNT(*) AS how_many
FROM book
GROUP BY subject
HAVING COUNT(*) > 1;

+-------------+----------+
| subject     | how_many |
+-------------+----------+
| Programming |        2 |
| Databases   |        3 |
+-------------+----------+

Only the subjects with more than one book. That cannot be done with WHERE, because at the time WHERE runs there are no groups yet and so no counts:

SELECT subject, COUNT(*) FROM book WHERE COUNT(*) > 1 GROUP BY subject;

ERROR 1111 (HY000): Invalid use of group function

Error 1111, invalid use of group function, and the evaluation order from [Practical 4: Simple Queries] explains it exactly: WHERE runs before GROUP BY.

The two are often used together, and then each does its own job:

SELECT subject, COUNT(*) AS how_many, ROUND(AVG(price), 2) AS average
FROM book
WHERE price > 300
GROUP BY subject
HAVING COUNT(*) > 1
ORDER BY average DESC;

+-------------+----------+---------+
| subject     | how_many | average |
+-------------+----------+---------+
| Databases   |        2 |  609.75 |
| Programming |        2 |  502.50 |
+-------------+----------+---------+

Read it in the server's order: take the books costing more than 300, pile them by subject, throw away the piles with only one book in them, work out the count and the average for the piles that are left, and sort.

Practical 4: Aggregate Functions, GROUP BY and HAVING

	`WHERE`	`HAVING`
Filters	rows	groups
Runs	before `GROUP BY`	after it
May use an aggregate	no	yes
May use a column not in the `GROUP BY`	yes	no
Without a `GROUP BY`	ordinary filtering	treats the whole table as one group

Grouping by more than one column

SELECT m.course, b.subject, COUNT(*) AS borrowings
FROM loan l
JOIN member m ON m.member_id = l.member_id
JOIN book   b ON b.book_id   = l.book_id
GROUP BY m.course, b.subject
ORDER BY m.course, b.subject;

+--------+-------------+------------+
| course | subject     | borrowings |
+--------+-------------+------------+
| BSc CS | Networks    |          1 |
| BSc CS | Programming |          1 |
| BSc IT | Databases   |          3 |
| BSc IT | Programming |          2 |
+--------+-------------+------------+

One row per combination that actually occurs. Combinations nobody borrowed do not appear at all, which is a real limitation: a grouped query cannot show a zero for a group that has no rows. To show those, start from the table that has all the values and use an outer join, which is [Practical 6: The Outer Join].

The joins in that query are [Practical 6: The Inner Join]; they are used here because a grouping worth doing usually spans two tables.

What beginners get wrong

Putting a plain column in the SELECT that is not in the GROUP BY. Error 1055, and worse on a server that allows it.

An aggregate in the WHERE. Error 1111. It belongs in HAVING.

Expecting COUNT(column) to count every row. It skips the NULLs. COUNT(*) does not.

Expecting AVG to divide by the number of rows. It divides by the number of non-NULL values.

Using HAVING where WHERE would do. It works, and it makes the server group rows it is about to throw away.

Forgetting ORDER BY. A GROUP BY does not promise an order.

Reading a count of rows as a count of things. COUNT(*) on a join counts matched pairs; the number of distinct books in that result is COUNT(DISTINCT book_id).

Quick revision

Five aggregates: COUNT, SUM, AVG, MIN, MAX.
Every one except COUNT(*) ignores NULLs.
COUNT(*) counts rows, COUNT(col) counts non-NULL values, COUNT(DISTINCT col) counts different ones.
GROUP BY makes one result row per group.
Every SELECT column must be in the GROUP BY or inside an aggregate; ONLY_FULL_GROUP_BY enforces it. Error 1055.
WHERE filters rows before grouping; HAVING filters groups after.
An aggregate in a WHERE is error 1111.
A group with no rows does not appear at all.

What goes in your journal

Aim, then each of the five functions once over the whole table, then a GROUP BY with a count, then the same query with HAVING, and finally one query using WHERE, GROUP BY, HAVING and ORDER BY together. Six or seven queries with their results.

Practical 4: Aggregate Functions, GROUP BY and HAVING

Add the COUNT(*) against COUNT(returned_on) pair and one line saying why the numbers differ. That pair is the evidence that you know the NULL rule, and it is the question that will be asked.

Test yourself

1. Which aggregate does not ignore NULLs? COUNT(*). It counts rows regardless of their contents; every other aggregate, including COUNT(column), skips NULLs.

2. What is the difference between WHERE and HAVING? WHERE filters individual rows before they are grouped. HAVING filters whole groups afterwards and may use aggregate functions.

3. Why is SELECT subject, title, COUNT(*) FROM book GROUP BY subject; refused? Because title is neither in the GROUP BY nor inside an aggregate, so there is no single title for a group of several books.

4. AVG over a column with NULLs: what is it the average of? Of the non-NULL values only. The NULL rows are not counted in the divisor.

5. Why can you not write WHERE COUNT(*) > 1? Because WHERE is evaluated before GROUP BY, so no groups and no counts exist yet. Use HAVING.

6. A subject with no books at all: does it appear in a GROUP BY subject result? No. A group with no rows produces no row. Showing it requires an outer join from a table that lists every subject.

The rest of this subject

These notes are cut from the University's printed syllabus. Open the syllabus itself for the same subject.

Syllabus for Major Practical 1

Report or request