This article will help you with understating GROUP BY and HAVING commands inside SQL language.
- GROUP BY clause
GROUP BY allows to group rows by category. We can combine them with aggregate functions (like SUM, COUNT etc).
# typical use of GROUP BY
SELECT customer_id FROM payment GROUP BY customer_id;
# sum amounts by customer
SELECT customer_id, SUM(amount) FROM payment GROUP BY customer_id;- HAVING clause
If you want to filter query after an aggregation, you can use HAVING clause.
Let's look at the example above:
SELECT customer_id, SUM(amount) FROM payment GROUP BY customer_id;If you want to filter customers, there is no problem:
SELECT customer_id, SUM(amount) FROM payment WHERE customer_id < 50 GROUP BY customer_id;But what if you want to select customers which SUM of amounts is higher than 50? You can't use WHERE anymore,
because this clause is happening after SUM clause - you basically don't have access to this data yet.
This is where HAVING clause becomes necessary.
SELECT customer_id, SUM(amount) FROM payment GROUP BY customer_id HAVING SUM(amount) > 50;