How to use SQL GROUP BY and HAVING clauses

Category: SQL :: Published at: 27.12.2021

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;


- Click if you liked this article

Views: 75