Autor: Codenga 12.04.2023

In SQL, we often create queries that group data. If you have some basic experience with this language, you probably know the GROUP BY clause.

Sometimes we want to add some criteria to our grouping - in other words, we want to group data that meets certain conditions. To do this, we can use the HAVING clause. In this article, we will show the basics of using HAVING, with the help of a simple example. Let's get started!

The table

Here is our example table:

The table is named orders. It has three columns: an unique identifier, customer ID (customer_id), and the total price of the order (total_price). There is nothing particularly complicated here.

Note that some customers have placed multiple orders. First, we will group the data using the customer_id column. This way, we will know the total order value for each customer. We will group the data by the customer_id column and use the SUM() function to calculate the sum of values in the total_price column for each unique customer.

SELECT customer_id, SUM(total_price) as total
FROM orders
GROUP BY customer_id;

Here is the result:

Our result is a set of four unique customers who have placed orders.

HAVING clause in action

We want to group only those customers who have placed orders with a total value exceeding 1000. To do this, we will use the HAVING clause. Take a look at the query:

SELECT customer_id, SUM(total_price) as total
FROM orders
GROUP BY customer_id
HAVING SUM(total_price) > 1000;

The last line, HAVING SUM(total_price) > 1000, should be interpreted as follows: "Include only those customers who have a total order value (i.e., SUM(total_price)) greater than 1000." The result of our query is two customers: 101 and 103. Look at the table - only these customers have a total orders value exceeding 1000.

Of course, the criteria can be changed as desired:

HAVING SUM(total_price) < 300;

What result will we get? We will get the customers who have a total order value less than 300. In our table, there is only one customer who meets this criterion: the customer with an ID of 104.

Summary

Using the HAVING clause allows us to add criteria to grouped data. We can group data that meets a specific condition thanks to it. That's why we most often use the HAVING clause in combination with GROUP BY.

Would you like to learn more useful SQL techniques? With the Fundamentals of SQL Level 3 course , you will master many practical solutions that will allow you to work with data freely. You will learn everything using practical tasks and exercises.