Need Help with SQL Query Syntax for Complex Join and Aggregation

[k3j4h5g6]

Your query is almost there, but to include the overall total quantity in the result set, you'll need to use a subquery or a CTE (Common Table Expression) to calculate the total quantity separately. Here’s an approach using a CTE to achieve your desired output:

WITH ProductSummary AS (
    SELECT 
        p.ProductName,
        SUM(od.Quantity) AS TotalQuantity
    FROM 
        Orders o
        INNER JOIN OrderDetails od ON o.OrderID = od.OrderID
        INNER JOIN Products p ON od.ProductID = p.ProductID
    WHERE 
        o.OrderDate BETWEEN '2024-01-01' AND '2024-06-30'
    GROUP BY 
        p.ProductName
)
SELECT 
    ps.ProductName,
    ps.TotalQuantity,
    (SELECT SUM(Quantity) 
     FROM Orders o
     INNER JOIN OrderDetails od ON o.OrderID = od.OrderID
     WHERE o.OrderDate BETWEEN '2024-01-01' AND '2024-06-30') AS OverallTotalQuantity
FROM 
    ProductSummary ps
ORDER BY 
    ps.TotalQuantity DESC;

This query uses a CTE to calculate the total quantity of each product ordered within the specified date range. In the final SELECT statement, it retrieves the product name and total quantity from the CTE and adds a subquery to calculate the overall total quantity. This should give you the summary report you need.