The `GROUP BY` clause in SQL is used to AGGREGATE identical data into groups based on one or more columns. It is often paired with aggregate functions, such as COUNT, SUM, AVG, MIN, and MAX, to perform calculations on each group of data.
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!
Morning!
I spoke with some of the operations managers and I was excited to learn that we now measure the most recent occupancy rate of all our restaurants. Can you confirm if this is available?
If it is, i’d like to average occupancy of all our restaurants
Best Regards,
Mrs Ahmadu (CGO)
SQL TOPIC OF THE DAY
Understanding the GROUP BY Clause
In this blog post, we’ll explore the `GROUP BY` command, its syntax, and various use cases to better understand how it can be leveraged in SQL queries. The basic syntax for the `GROUP BY` clause is as follows:
SELECT column1, aggregate_function(column2) FROM table_name GROUP BY column1 |
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.
Common Use Cases for GROUP BY
The GROUP BY clause is used in various scenarios:
1. Aggregating Data by Category:
Consider a table named sales that contains information about sales transactions, including columns such as product, quantity_sold, and price. To find the total quantity sold for each product, you can use the following query:
SELECT product, SUM(quantity_sold) as total_quantity FROM sales GROUP BY product ; |
2. Analyzing Time-Based Data:
For time-based data, the GROUP BY clause is often used to analyze trends over specific time intervals. Let’s say you have a table named orders with columns like order_date and total_amount. To find the total sales for each month, you can use:
SELECT MONTH(order_date) as month, SUM(total_amount) as monthly_sales FROM orders GROUP BY MONTH(order_date); |
3. Finding Duplicates or Unique Values:
The GROUP BY clause is also useful for identifying duplicate records or finding unique values in a column. Suppose you have a table employees with a column department, to find the number of employees in each department, you can use:
SELECT department, COUNT(*) as employee_count FROM employees GROUP BY department; |
SQL PRACTICE
Now, let’s write a script to answer the question of the day.
Mrs Ahmadu would like to get the average occupancy of all our restaurants
SELECT name, AVG(occupancy) FROM restaurant GROUP BY name; |
FEEDBACK FROM THE CGO
Thank you for the quick turnaround! Kindly provide the total occupancy per country as well. Will that be possible by EOD?
Thanks!
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!