GROUP BY and HAVING in MySQL | Aggregation in MySQL

GROUP BY

The GROUP BY clause is used to arrange the rows in a group using a particular column value. If there are multiple rows with the same value for a column then all those rows will be grouped with that column value. It is used with the SELECT statement and is placed after the WHERE clause and before the ORDER BY if used any.

Syntax:

SELECT column1,column2,…
FROM table_name
WHERE condition
GROUP BY column1,column2, …
Order BY column1, column2, ….

The GROUP BY clause is generally used with aggregate functions like SUM, AVG, COUNT, MAX, MIN. The aggregate functions provide information about each group.

1. GROUP BY clause without aggregate functions

For example:

SELECT country
FROM cricketers
GROUP BY country; 

Output:

In the above example, the country is selected and grouped according to the country which ensures unique occurrences of each country.

2. GROUP BY clause with aggregate functions

For example:

SELECT COUNT(name) AS number_of_players, country
FROM cricketers
GROUP BY country;

Output:

Similarly, when GROUP BY is used with multiple columns, all those rows will be grouped which have the same value for all the columns used for grouping.

HAVING

The HAVING clause is used with the GROUP BY clause in a query to specify come conditions and filter some groups or aggregates that fulfill those conditions. The difference between WHERE and HAVING is, WHERE is used to filter rows, and HAVING is used to filter groups by applying some conditions.

Syntax:

SELECT column1, column2, …
FROM table_name
WHERE conditions
GROUP BY column1, column2, …..
HAVING conditions

For example:

SELECT COUNT(name) AS number_of_players, country
FROM cricketers
GROUP BY country
HAVING number_of_players>1;

Output:

In the above example, the players are grouped according to their country and those countries which have the number of players >1 are selected using the HAVING clause.