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; |
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; |
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; |
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; |
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!