Skip to content

Understanding the HAVING clause

While the WHERE clause filters individual rows before they are grouped, the HAVING clause filters groups of rows after the grouping has occurred. This makes it particularly useful for filtering aggregated data

There are many ways to build SQL competency so we have decided to take a different approach. For the next few days, you will be serving as the newly hired Data Analyst for Basira and Co, An Afro fusion restaurant with a network of chains across Africa and you’ll be reporting to the Chief Growth Officer (CGO) Mrs Ahmadu. 

Everyday, you’ll get analytical requests/tasks from her related to the SQL topic of the day. The goal for this is to make each lesson as practical and as relatable as possible.

HOW TO PRACTICE: Here is a link to our practice guide. We have already prepared the table schema in advance so all you need to do is plug that into DBFiddle.com and you are all set! The LinkedIn forum is a great way to share any comments or questions about the practice guide.

YOU’VE GOT MAIL!

Good Morning!
After reviewing the table you shared yesterday, it seems we can still get more insights from the data. I’d like see the number of all restaurants per country where average occupancy is greater than 50%

You can take your time with this task, I won’t need it till tomorrow.
Best Regards,
Mrs Ahmadu (CGO)

SQL TOPIC OF THE DAY 

The HAVING clause is used in conjunction with the GROUP BY clause to filter the results of a grouped query based on specified conditions.
The basic syntax for the HAVING clause is as follows:

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition;
column1: is the column by which the data is grouped.
aggregate_function(column2): is the aggregate function applied to another column.
table_name: The name of the table from which you want to fetch data.
condition: is the condition applied to the aggregated data.

Common Use Cases for GROUP BY
The GROUP BY clause is used in various scenarios:

1. Filtering Aggregated Results:

Consider a scenario where you have a table named sales with columns such as product, quantity_sold, and price. To find products with a total quantity sold greater than 100, you can use the following query:

SELECT  product, SUM(quantity_sold) as total_quantity
FROM sales
GROUP BY product
HAVING SUM(quantity_sold) > 100;
This query groups the data by the `product` column and filters the results to include only those with a total quantity sold greater than 100.

2. Applying Multiple Conditions:

The HAVING clause allows for the application of multiple conditions to the aggregated data. For instance, let’s say you want to find products with a total quantity sold greater than 50 and an average price higher than $10:

SELECT product, SUM(quantity_sold) as total_quantity, AVG(price) as avg_price
FROM sales
GROUP BY product
HAVING SUM(quantity_sold) > 50 AND AVG(price) > 10;
This query combines two conditions to filter the results based on both the total quantity sold and the average price.

3. Filtering Time-Based Data:

When dealing with time-based data, the HAVING clause can be used to filter results based on specific time intervals. Suppose you have an orders table with columns like order_date and total_amount. To find months with total sales greater than $500, you can use:

SELECT MONTH(order_date) as month, SUM(total_amount) as monthly_sales
FROM orders
GROUP BY MONTH(order_date)
HAVING SUM(total_amount) > 500;
This query groups the data by the month extracted from the order_date column and filters the results to include only those with total sales greater than $500.

SQL PRACTICE 

Now, let’s write a script to answer the question of the day.
Mrs Ahmadu would like to know the number of restaurants where average occupancy is greater than 50% per country.

SELECT country, COUNT(id) as no_of_restaurants
FROM restaurants
GROUP BY country
HAVING AVG(occupancy) > 0.5;

FEEDBACK FROM THE CGO

That was fast… 
I shared the numbers with the management team and we’d also like to see the number of all restaurants where average occupancy is less than 30%. I’ll wait for your response on Monday morning.
Enjoy the weekend!
Warm regards,
Mrs Ahmadu (CGO

With what we learned today, think you can solve this part on your own?
Did you learn anything new? Share your new insights with us on Facebook, LinkedIn or Instagram and don’t forget to tag us!

Loading spinner

Previous Posts