MySQL GROUP BY & HAVING clause

To group rows into subgroup by one or more values of columns we use MySQL GROUP BY.

Suppose in user table same country name exists for many times for different user. So if we use GROUP BY for country column to a SELECT query then only unique country will be listed.

EXAMPLE:

SELECT * FROM users GROUP BY user_country;

It works same like DISTINCT operator. But the different is aggregate functions like SUM, AVG, MAX, MIN and COUNT are used by this clause.

The aggregate functions is used to perform calculation on each subgroup and return in a single value result for each subgroup.

SELECT COUNT(*) FROM users GROUP BY user_country;

will show the no of users for each unique country.

MySQL HAVING clause is used to filter conditions for group of rows. HAVING clause is used with the GROUP BY clause.

EXAMPLE:

SELECT * FROM users GROUP BY user_country HAVING age > 18;