SQL Query for three tables with calculation

Performing Calculations Across Multiple SQL Tables

Learn how to join three SQL tables, aggregate data, and perform calculations to derive meaningful insights from your database.

In many real-world database scenarios, the data required for a specific report or analysis is not confined to a single table. Instead, it's often distributed across several related tables. This article will guide you through the process of querying data from three distinct tables, joining them effectively, and then performing calculations on the combined dataset. We'll use a common scenario involving Orders, OrderDetails, and Products tables to illustrate these concepts, focusing on calculating the total revenue per order.

Understanding the Database Schema

Before writing any SQL queries, it's crucial to understand the relationships between your tables. For our example, consider three tables: Orders, OrderDetails, and Products.

• Orders: Contains general order information like OrderID and OrderDate.
• OrderDetails: Links Orders to Products, storing OrderID, ProductID, Quantity, and UnitPrice (at the time of order).
• Products: Holds product-specific information like ProductID and ProductName.

The OrderDetails table acts as a bridge, connecting Orders to Products through OrderID and ProductID respectively. This many-to-many relationship is fundamental for calculating order totals based on product prices and quantities.

erDiagram
    ORDERS ||--o{ ORDER_DETAILS : "has"
    PRODUCTS ||--o{ ORDER_DETAILS : "contains"
    
    ORDERS {
        int OrderID PK
        date OrderDate
        int CustomerID FK
    }
    ORDER_DETAILS {
        int OrderDetailID PK
        int OrderID FK
        int ProductID FK
        int Quantity
        decimal UnitPrice
    }
    PRODUCTS {
        int ProductID PK
        varchar ProductName
        decimal StandardPrice
    }

Entity-Relationship Diagram for Orders, OrderDetails, and Products tables.

Joining Tables for Comprehensive Data

To perform calculations that span across these tables, we first need to join them. INNER JOIN is the most suitable type of join here, as we only want to include orders that have corresponding details and products that exist in the OrderDetails table. We'll join Orders with OrderDetails on OrderID, and then OrderDetails with Products on ProductID.

SELECT
    o.OrderID,
    o.OrderDate,
    p.ProductName,
    od.Quantity,
    od.UnitPrice
FROM
    Orders o
INNER JOIN
    OrderDetails od ON o.OrderID = od.OrderID
INNER JOIN
    Products p ON od.ProductID = p.ProductID;

SQL query to join Orders, OrderDetails, and Products tables.

Performing Calculations and Aggregation

Once the tables are joined, we can perform calculations. A common requirement is to calculate the total amount for each item in an order (Quantity * UnitPrice) and then sum these item totals to get the total revenue for each order. This involves using an aggregate function (SUM) and grouping the results by OrderID.

SELECT
    o.OrderID,
    o.OrderDate,
    SUM(od.Quantity * od.UnitPrice) AS TotalOrderRevenue
FROM
    Orders o
INNER JOIN
    OrderDetails od ON o.OrderID = od.OrderID
INNER JOIN
    Products p ON od.ProductID = p.ProductID
GROUP BY
    o.OrderID, o.OrderDate
ORDER BY
    o.OrderDate DESC, TotalOrderRevenue DESC;

SQL query to calculate total revenue per order by joining and aggregating data.

This query first calculates the (Quantity * UnitPrice) for each item in an order. Then, SUM() aggregates these values for each OrderID, providing the TotalOrderRevenue. The GROUP BY clause is essential here, as it tells the database to perform the SUM operation for each unique OrderID and OrderDate combination. Finally, ORDER BY sorts the results, making them easier to analyze.