Using the SQL HAVING Clause

Mastering the HAVING Clause to Filter Aggregated Results in SQL

Introduction

The SQL HAVING clause is a powerful tool that allows you to filter the results of a query based on a specified condition. It is primarily used in combination with the GROUP BY clause to work on aggregated functions such as COUNT, SUM, AVG, and others. In this article, we will dive into the usage of the HAVING clause, understand its importance, and explore examples to help you implement it effectively in your SQL queries.

Understanding the HAVING Clause

The HAVING clause is used to filter the results of aggregated functions. It is applied after the GROUP BY clause and helps you refine the query results according to certain criteria. When filtering based on columns that are not aggregated, the WHERE clause should be used instead.

Examples

Imagine you have a “sales” table with the following columns: id, product_id, date, and quantity.

Here’s an example of a query using the HAVING clause to show the products that have sold more than 10 units in total:

SELECT product_id, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_id
HAVING total_quantity > 10;

In this query:

The GROUP BY clause groups the rows by the product_id column.

The SUM function calculates the total quantity for each product_id.

The HAVING clause filters the results, showing only the product_ids with a total_quantity greater than 10.

Remember, the HAVING clause is used when you need to filter the results of aggregated functions. If you’re filtering based on a column that is not aggregated, you should use the WHERE clause instead.

Another Example

Imagine you have an “orders” table with the following columns: order_id, customer_id, order_date, and total_price.

Let’s say you want to find all customers who have made at least 3 orders with a total_price greater than 50. Here’s an example of a query using the HAVING clause:

SELECT customer_id, COUNT(order_id) AS number_of_orders, SUM(total_price) AS total_spent
FROM orders
WHERE total_price > 50
GROUP BY customer_id
HAVING number_of_orders >= 3;

In this query:

The WHERE clause filters the rows, showing only the orders with a total_price greater than 50.

The GROUP BY clause groups the filtered rows by the customer_id column.

The COUNT function calculates the number of orders for each customer_id.

The SUM function calculates the total amount spent for each customer_id.

The HAVING clause filters the results, showing only the customer_ids with a number_of_orders greater than or equal to 3.

A Final Example

Imagine you have a “students” table with the following columns: student_id, class_id, and score.

Let’s say you want to find all classes where the average score is above 85. Here’s an example of a query using the HAVING clause:

SELECT class_id, AVG(score) AS average_score
FROM students
GROUP BY class_id
HAVING average_score > 85;

In this query:

The GROUP BY clause groups the rows by the class_id column.

The AVG function calculates the average score for each class_id.

The HAVING clause filters the results, showing only the class_ids with an average_score greater than 85.