How to use SQL Aggregate Functions

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

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.

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 ;

This query groups the data by the `product` column and calculates the sum of `quantity_sold` for each 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);

This groups the data by the month extracted from the `order_date` column and calculates the total sales for each month.

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;

This query groups the data by the `department` column and provides a count of employees in each 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;

Instead of writing out the full column names, you can also use the column position as an alias e.g. 1,2,3,…

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!