Grouping Data: Using GROUP BY and HAVING Clauses

Grouping data in MySQL matters once your pages get past the basics. Using GROUP BY to summarise rows and HAVING to filter those summaries.

The GROUP BY clause

GROUP BY collects identical values into groups. You use it with aggregate functions like COUNT, SUM, or AVG to summarise data.

Basic GROUP BY

Count the number of sales per day from a sales table:

SELECT sale_date, COUNT(*) 
FROM sales 
GROUP BY sale_date;

That groups sales by date and counts how many fall on each date.

Grouping by multiple columns

Count sales by date and product:

SELECT sale_date, product_id, COUNT(*) 
FROM sales 
GROUP BY sale_date, product_id;

One count per product per date.

Aggregate functions

Aggregate functions calculate a single value from a set of rows. They work with GROUP BY:

• COUNT: number of rows in a group.
• SUM: total of values in a group.
• AVG: average of values in a group.
• MAX/MIN: highest or lowest value in a group.

Total sales amount per day:

SELECT sale_date, SUM(amount) 
FROM sales 
GROUP BY sale_date;

The HAVING clause

WHERE filters rows before grouping. HAVING filters groups after grouping has happened.

Basic HAVING

Days with more than 50 sales:

SELECT sale_date, COUNT(*) 
FROM sales 
GROUP BY sale_date 
HAVING COUNT(*) > 50;

Only dates where the total count exceeds 50 are returned.

GROUP BY and HAVING with aggregates

Products with an average sale amount over £100:

SELECT product_id, AVG(amount) 
FROM sales 
GROUP BY product_id 
HAVING AVG(amount) > 100;

A few practical tips

1. Group on columns that make sense: do not over-complicate it.
2. Use aliases: name your aggregate results so the output is easy to read.
3. Mind performance: grouping large datasets can be slow. Indexing helps.

Wrapping up

GROUP BY and HAVING turn raw rows into summaries – sales per day, averages per product, that sort of thing. Try different groupings and HAVING conditions on your own data and you will quickly see how useful they are for reports and analysis.