A Quick Guide to SQL — Chapter 5: Aggregating Data

3 min readJun 16, 2023

In the previous chapter we talked about joining tables in SQL. In this chapter, we will explore how to aggregate data in SQL using the GROUP BY clause and aggregate functions. Aggregating data allows us to calculate summary information such as totals, averages, counts, and more. We will learn how to group data based on specific columns and perform calculations on each group. By the end of this lesson, you will have a solid understanding of how to summarize and analyze data using SQL. Let’s get started!

Chapters of This Series

1. Understanding Aggregate Functions

Aggregate functions perform calculations on a set of values and return a single result. Some commonly used aggregate functions include:

SUM: Calculates the sum of values in a column.
AVG: Computes the average of values in a column.
COUNT: Counts the number of rows or non-null values in a column.
MIN: Retrieves the minimum value from a column.
MAX: Retrieves the maximum value from a column.

2. Using GROUP BY Clause

The GROUP BY clause is used to group rows based on one or more columns. It allows us to aggregate data and perform calculations within each group. Let’s assume we have a table called “orders” with columns “order_id,” “customer_id,” and “order_amount.” To calculate the total order amount for each customer, use the following syntax:

SELECT customer_id, SUM(order_amount) AS total_amount
FROM orders
GROUP BY customer_id;

This statement groups the rows by “customer_id” and calculates the sum of “order_amount” for each customer, assigning the alias “total_amount” to the calculated value.

3. Filtering Grouped Data with HAVING

Clause The HAVING clause works similar to the WHERE clause but is used specifically for filtering grouped data. It allows us to specify conditions for the aggregated values. For example, let’s retrieve customers who have placed orders with a total amount greater than $500:

SELECT customer_id, SUM(order_amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING SUM(order_amount) > 500;

The HAVING clause filters the groups based on the condition specified.

4. Using Aggregate Functions with Other Columns

Aggregate functions can be used alongside other columns in the SELECT statement. For instance, let’s retrieve the total order amount, average order amount, and the number of orders for each customer:

SELECT customer_id, SUM(order_amount) AS total_amount, AVG(order_amount) AS average_amount, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

This statement calculates the total order amount, average order amount, and order count for each customer.

5. Nesting Aggregate Functions

Aggregate functions can also be nested within each other to perform more complex calculations. Let’s calculate the average order amount for customers who have placed more than five orders:

SELECT AVG(total_amount) AS average_amount
FROM (
  SELECT customer_id, SUM(order_amount) AS total_amount
  FROM orders
  GROUP BY customer_id
  HAVING COUNT(*) > 5
) AS subquery;

In this example, we use a subquery to calculate the total order amount for each customer, and then we calculate the average order amount for customers with more than five orders.

6. Conclusion

In this lesson, we explored the powerful capabilities of aggregating data in SQL using the GROUP BY clause and aggregate functions. We learned how to group rows based on specific columns and perform calculations on each group using functions like SUM, AVG, COUNT, MIN, and MAX. We also saw how to filter grouped data using the HAVING clause and use aggregate functions alongside other columns. With these techniques, you can efficiently summarize and analyze data, gaining valuable insights from your SQL databases.

If you found this course helpful and would like to explore more free courses, I invite you to follow my account on Medium and connect with me on LinkedIn. I regularly share valuable content on these platforms.