Group By & Having

The GROUP BY statement groups rows with the same values into summary rows.

Its often used with aggregate functions (Count, Min, Max, Sum, Avg)

Simple Syntax:

Select column(s)
From tablename
Group By column(s)

Example:

select * from Chinook.Customer order by country;

How to find out the number of countries in the table

SELECT  country,count(*) FROM  Chinook.Customer group by country;

Does this result make sense?

Select count(*) from Chinook.Invoice group by CustomerId;

What do you think about it now?

Select CustomerId,count(*) from Chinook.Invoice group by CustomerId;

Select CustomerId ,count(*),min(total),max(total), sum(total), avg(total) from Chinook.Invoice group by CustomerId;

Group By Having

Having clause is used in SQL to filter Grouped data.

Wait.. if Where does filtering.. why need Having?

Let's learn more

SELECT
    COUNT(country)
    ,Country
FROM
    Customer
GROUPBY
    Country
HAVING
    COUNT(country) >5;

Where clause filters on the entire dataset.

The Having clause filters on the grouped dataset.

Note: When Having is used without Group By, it acts like a Where clause. (this feature is available in current MySQL)