Databases & SQL · Class 12 · Aggregate Functions & Grouping

MySQLaggregates⏱️ 9 min read

GROUP BY

max(sal) gives the highest salary in the school. The more useful question is the highest salary in each city — one answer per city, from one query. That is what group by is for.

1Sort the rows into piles

Picture the twelve teachers as cards on a desk. Deal them into piles, one pile per city. Now run max(sal) separately on each pile. You get one answer per pile, not one answer overall.

1. Split

Rows are grouped by the value in the named column.

2. Aggregate

The function runs once inside each group.

3. One row each

The resultset has one row per group, not per teacher.

Run the three steps below before reading on — the middle one is the part a finished resultset never shows you.

Watch the piles form

Twelve teachers go in. Five rows come out — one per city, not one per teacher.

mysql> select name, sal, city from teachers;

Sunita Rai62000

Mohan Das58000

Tashi Bhutia47000

Reena Gupta44500

Anil Sharma34000

Kabita Subba31500

Deepak Chettri45000

Maya Lepcha54000

Rajesh Kumar60000

Sarita Pradhan46000

Bikash Tamang33000

Nirmala DeviNULL

12 rows in set — one per teacher

Twelve separate rows. Nothing has been grouped yet — this is what where would filter.

2The highest salary in each city

MySQL command line client

mysql> select max(sal), city from teachers group by city;

+----------+----------+

| max(sal) | city |

+----------+----------+

| 62000 | Singtam |

| 58000 | Gangtok |

| 60000 | Namchi |

| 46000 | Siliguri |

| 54000 | Geyzing |

+----------+----------+

5 rows in set (0.00 sec)

Twelve teachers became five rows — one per city. Each number is the top salary within that city, and 62000 is the largest of them, which matches the plain max(sal) from two lessons ago. Grouping did not change the data; it changed how it was divided up before the function ran.

3Counting within each group

count(*) with group by is the commonest combination of the two, and answers “how many in each…?”:

MySQL command line client

mysql> select count(*), city from teachers group by city;

+----------+----------+

| count(*) | city |

+----------+----------+

| 2 | Singtam |

| 3 | Gangtok |

| 3 | Namchi |

| 2 | Siliguri |

| 2 | Geyzing |

+----------+----------+

5 rows in set (0.00 sec)

Two plus three plus three plus two plus two is twelve, so every teacher is in exactly one pile. That sum is a quick way to check a grouped result.

MySQL command line client

mysql> select count(*), grade from students group by grade;

+----------+-------+

| count(*) | grade |

+----------+-------+

| 3 | 10 |

| 4 | 12 |

| 2 | 11 |

| 1 | 8 |

| 2 | 9 |

+----------+-------+

5 rows in set (0.00 sec)

The groups are not sorted

Grade 10 came out first, then 12, then 11. group by makes the piles but does not arrange them. Add order by grade on the end when the order matters — and it usually does in a report.

4NULL gets a pile of its own

One teacher has no designation recorded. Group by that column and the missing values are gathered together, just as distinct gathered them:

MySQL command line client

mysql> select max(sal), designation from teachers group by designation;

+----------+-------------+

| max(sal) | designation |

+----------+-------------+

| 62000 | PGT |

| 47000 | TGT |

| 34000 | PRT |

| 54000 | NULL |

+----------+-------------+

4 rows in set (0.00 sec)

Four groups: three real designations and one for “not recorded”. This is the same behaviour as distinct and the opposite of the aggregates, which skip NULLs. Grouping is about the column you group by; skipping is about the column you aggregate.

5The rule that catches everyone

Every plain column in the select must also appear in the group by.

In every example above, the only non-aggregate column selected — city, grade, designation — is the one being grouped by. That is not a coincidence.

Ask for select name, max(sal) from teachers group by city; and the query is asking for a single name from a pile of three teachers. There is no sensible answer, so it is not a sensible question.

Two kinds of column, and only two

In a grouped query, each selected column must be either the thing you grouped by (the same for every row in the pile) or an aggregate (which turns the pile into one value). Anything else has no single value to show.

6Where the clause goes

select   <columns and aggregates>
  from   <table>
 where   <condition on rows>
 group by <column>
 order by <column>;

The order is fixed, and it is also the order things happen in: where throws rows away first, then the survivors are grouped, then the result is sorted. Because where runs before the grouping, it can only test individual rows — never a group total. Filtering the groups themselves is the next lesson.