GROUP BY, HAVING

GROUP BY is used with aggregate function to group the result based on one or more columns.

SYNTAX
SELECT comma_separated_column_names
FROM table_name
WHERE condition
GROUP BY column_namesORDER BY column_names;

EXAMPLE1
SELECT COUNT(id), city
FROM users
GROUP BY city

Note: Above query will retrieve count of total number of users available in each city.

EXAMPLE2
SELECT count(orders.order_id) Total Orders, username 
FROM users 
LEFT JOIN orders
ON orders.user_id=users.id 
GROUP BY username

Note: Above query will retrieve total number of orders made by each user.

HAVING
It is used to provide condition with the group by clause.

EXAMPLE1
SELECT COUNT(id), city
FROM users
GROUP BY city
HAVING COUNT(id) > 2

Note: Above query will retrieve all the city having minimum two users in it.

EXAMPLE2
SELECT count(orders.order_id) Total Orders, username 
FROM users 
LEFT JOIN orders
ON orders.user_id=users.id 

GROUP BY username
HAVING count(orders.order_id) > 2

Note: Above query will retrieve all the users having minimum two oders.