Question
When do we use the HAVING clause, and how does it relate to the GROUP BY clause?
Answer
The GROUP BY clause will group together all rows that have the same value in the column(s) specified, and collapse them into a single row in the returned results. (Note: It is often used with aggregate functions in the SELECT statement for the non-grouped columns). For example:
-- returns total orders for each month
SELECT month, COUNT(order_id)
FROM tbl
GROUP BY 1;
When we use GROUP BY, we also have the option to use the HAVING clause to filter these groups even further:
-- returns total orders for each month, but only for months where total is greater than 100
SELECT month, COUNT(order_id)
FROM tbl
GROUP BY 1
HAVING COUNT(order_id) > 100;