Master Joining 3 Tables in SQL with GROUP BY for Advanced Data Aggregation

When working with relational data in SQL, it's common to need to combine data from multiple tables. One way to do this is by joining tables. However, SQL also provides the GROUP BY clause to group rows that have the same values in specified columns into aggregate values, typicallySUM or COUNT. This article explores how to join three tables in SQL and use the GROUP BY clause to manipulate and understand your data better.

three vennuous circles with the words right table, left table and right table

Before we dive into the specifics, let's ensure we have a basic understanding of the SQL JOIN and GROUP BY commands. The JOIN clause is used to combine rows from two or more tables based on a related column between them. GROUP BY is used to group rows that have the same values in specified columns into aggregated data.

Group By in SQL With Example [ COUNT, AVG, MAX, MIN ,SUM ]

Using JOIN and GROUP BY Together

The JOIN and GROUP BY clauses are often used together to combine data from multiple tables and aggregate the results. To join three tables, you typically use the INNER JOIN or LEFT JOIN keywords to combine the tables one after the other.

SQL Joins Explained with Examples | Beginner SQL Guide

Here's a simple example. Assume we have three tables: 'Customers', 'Orders', and 'OrderDetails'. We want to identify the top 5 customers by the total number of orders. Here's how you might do it:

Basic Joining and Grouping

MySQL for Beginners -Joining Tables.(Part-11)

First, let's write a statement to join these tables and group the results by the customer's ID:

```sql SELECT Customers.CustomerID, Customers.CustomerName, COUNT(Orders.OrderID) AS TotalOrders FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID GROUP BY Customers.CustomerID, Customers.CustomerName; ```

Adding an Aggregate Function

SQL GROUP BY Explained with Examples | Beginner Friendly SQL Guide

In this SQL query, we've used the COUNT() function, which is an aggregate function. Aggregate functions take one or more column values as input and return a single output value. Other common aggregate functions include SUM, MIN, MAX, AVG, among others.

For instance, you could calculate the total sales for each customer by summing up the 'Quantity' and 'UnitPrice' columns from the 'OrderDetails' table:

```sql SELECT Customers.CustomerID, Customers.CustomerName, SUM(OrderDetails.Quantity * OrderDetails.UnitPrice) AS TotalSales FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID GROUP BY Customers.CustomerID, Customers.CustomerName; ```

Filtering and Sorting Results

SQL's support for WHERE and ORDER BY makes it a flexible tool for data manipulation. You can add a WHERE clause after the joins to filter the results before grouping:

SQL GROUP BY Explained Visually (Beginner-Friendly Guide)

SQL Basics Cheat Sheet for Beginners | Learn SQL Fast 💻

Give me 2 mins—I'll teach you how SQL JOINs work: The Join clause lets… | Neo Kim | 82 comments

Complete SQL Cheat Sheet for Beginners | SQL Commands, Joins, Queries & Database Guide

a poster with the names and symbols for different types of letters

four circles with the names and numbers in each circle, labeled to be able to label them

SQL Joins Explained 🚀 | Beginner Data Analyst SQL Guide

SQL Cheatsheet PDF | SQL Commands, Joins & Queries Explained

SQL JOINS Made Easy 🔗 | INNER, LEFT, RIGHT & FULL JOIN with Examples

Types of Joins in SQL Explained Clearly 📊 | Inner Join, Left Join, Right Join, Full Join |

What is the purpose of the GROUP BY clause?

Filtering Results

For example, to only include orders from 2020, you might do:

```sql SELECT Customers.CustomerID, Customers.CustomerName, SUM(OrderDetails.Quantity * OrderDetails.UnitPrice) AS TotalSales FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID WHERE YEAR(Orders.OrderDate) = 2020 GROUP BY Customers.CustomerID, Customers.CustomerName; ```

Sorting Results

You can use ORDER BY to sort the results. Adding ORDER BY TotalSales DESC at the end of the previous query will arrange customers by their total sales in descending order:

```sql SELECT Customers.CustomerID, Customers.CustomerName, SUM(OrderDetails.Quantity * OrderDetails.UnitPrice) AS TotalSales FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID WHERE YEAR(Orders.OrderDate) = 2020 GROUP BY Customers.CustomerID, Customers.CustomerName ORDER BY TotalSales DESC; ```

Limiting the Results

You might want to limit the output to a certain number of records. For instance, to show the top 5 customers only, you would use the LIMIT clause:

Using LIMIT

Add LIMIT 5 at the end of the previous query to limit the output to the top 5 customers:

```sql SELECT Customers.CustomerID, Customers.CustomerName, SUM(OrderDetails.Quantity * OrderDetails.UnitPrice) AS TotalSales FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID WHERE YEAR(Orders.OrderDate) = 2020 GROUP BY Customers.CustomerID, Customers.CustomerName ORDER BY TotalSales DESC LIMIT 5; ```

Understanding how to join multiple tables with GROUP BY in SQL empowers you to manipulate and analyze complex data more effectively. Remember that practice is key to mastering SQL, so start using it in your projects today!