Grouping Data: GROUP BY and HAVING

Turn many rows into one tidy row per group

Big Idea: GROUP BY gathers rows that share the same value into groups, then runs an aggregate (like COUNT or SUM) once per group. HAVING then keeps or drops whole groups based on that aggregate.

By the end you will be able to:

form groups of rows with GROUP BY,
get one aggregate value per group (count, sum, average, min, max),
filter whole groups with HAVING,
tell WHERE (filters rows) apart from HAVING (filters groups),
avoid the classic mistake of selecting a column that is neither grouped nor aggregated.

1. What does "grouping" mean?

Imagine a big basket of clean laundry. Before you put it away, you sort it into piles: one pile of socks, one of shirts, one of towels. Now a question like "how many socks?" is easy, because the socks are all together in one pile.

Grouping in SQL does the same thing to rows. You pick a column, and every row that shares the same value in that column lands in the same pile. Then you can ask one question about each pile at the same time: how many? what is the total? what is the average?

In the last topic you learned aggregate functions such as COUNT, SUM, AVG, MIN and MAX. On their own they squeeze the whole table down to a single number. GROUP BY is what lets you get that number for each pile instead of for everything at once.

2. Our example table: a pet shop

We will use one small table for the whole topic: a list of pets for sale in a shop. Here is how it is built and filled.

CREATE TABLE pets (
  id      INT PRIMARY KEY,
  name    VARCHAR(20),
  species VARCHAR(10),
  age     INT,
  price   DECIMAL(6,2)
);

INSERT INTO pets (id, name, species, age, price) VALUES
  (1, 'Rex',    'dog',  3, 200.00),
  (2, 'Bella',  'dog',  5, 150.00),
  (3, 'Milo',   'cat',  2, 100.00),
  (4, 'Luna',   'cat',  4, 120.00),
  (5, 'Coco',   'cat',  1,  90.00),
  (6, 'Nemo',   'fish', 1,  20.00),
  (7, 'Goldie', 'fish', 2,  15.00);

The whole table (SELECT * FROM pets;):

id	name	species	age	price
1	Rex	dog	3	200.00
2	Bella	dog	5	150.00
3	Milo	cat	2	100.00
4	Luna	cat	4	120.00
5	Coco	cat	1	90.00
6	Nemo	fish	1	20.00
7	Goldie	fish	2	15.00

Notice the species column repeats: two dogs, three cats, two fish. That repeating column is the perfect thing to group by.

Grouping Data: GROUP BY and HAVING