SQL JOIN, GROUP BY on three tables to get totals

To perform a SQL JOIN and GROUP BY on three tables to calculate totals, follow these steps:

Example Scenario

Let’s assume you have three tables:

1. Customers: Contains customer information.

   CustomerID | Name
   1          | John
   2          | Jane
   3          | Alice
   

2. Orders: Contains order information.

   OrderID | CustomerID | OrderDate
   101     | 1          | 2023-12-01
   102     | 2          | 2023-12-02
   103     | 1          | 2023-12-03
   

3. OrderDetails: Contains details about items in each order.

   OrderDetailID | OrderID | Product | Quantity | Price
   1             | 101     | A       | 2        | 50
   2             | 101     | B       | 1        | 30
   3             | 102     | A       | 1        | 50
   4             | 103     | C       | 3        | 20
   

Goal

You want to calculate the total sales (sum of quantity × price) for each customer.

Query to Achieve This

Here’s how you can join the tables and group by the customer to calculate totals:

SELECT 
    c.CustomerID,
    c.Name,
    SUM(od.Quantity * od.Price) AS TotalSales
FROM 
    Customers c
JOIN 
    Orders o ON c.CustomerID = o.CustomerID
JOIN 
    OrderDetails od ON o.OrderID = od.OrderID
GROUP BY 
    c.CustomerID, c.Name
ORDER BY 
    TotalSales DESC; -- Optional, to sort by total sales

Explanation:

1. Joins:
   • Customers is joined to Orders using CustomerID.
   • Orders is joined to OrderDetails using OrderID.
2. Aggregation:
   • SUM(od.Quantity * od.Price) calculates the total sales for each customer.
3. GROUP BY:
   • Group by CustomerID and Name to calculate totals for each customer.
4. Ordering:
   • Use ORDER BY TotalSales DESC to sort customers by their total sales in descending order (optional).

Output

For the given data, the query would produce:

CustomerID | Name  | TotalSales
1          | John  | 190
2          | Jane  | 50
3          | Alice | NULL (No orders)

Notes:

• If a customer doesn’t have any orders, they will not appear in the result. Use a LEFT JOIN if you want to include all customers with a NULL total for those without orders.

  SELECT 
      c.CustomerID,
      c.Name,
      SUM(od.Quantity * od.Price) AS TotalSales
  FROM 
      Customers c
  LEFT JOIN 
      Orders o ON c.CustomerID = o.CustomerID
  LEFT JOIN 
      OrderDetails od ON o.OrderID = od.OrderID
  GROUP BY 
      c.CustomerID, c.Name;
  

Let me know if you need help adapting this query for your specific schema!