Need Help with SQL Query Syntax for Complex Join and Aggregation

[gregbowers]

Hello,


I am currently working on a project where I need to retrieve specific data from multiple tables in my SQL Server database. I have three tables: Orders, OrderDetails, and Products. Orders table contains order information with columns OrderID and OrderDate. OrderDetails table contains details about each order item with columns OrderID, ProductID, and Quantity. Products table contains product details with columns ProductID and ProductName.

What I need to achieve is to retrieve a summary report showing the total quantity of each product ordered within a specified date range; grouped by product name ; along with the total quantity ordered overall.

I am trying to write a SQL query to accomplish this but have been running into syntax errors and difficulties with the aggregation.
I have tried so far:

SELECT 
    p.ProductName,
    SUM(od.Quantity) AS TotalQuantity,
    -- Additional aggregation and grouping logic

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
ORDER BY 
    TotalQuantity DESC;

However, this query isn't yielding the correct results; and I'm struggling to properly aggregate the quantities and include the overall total in the same result set.

Could someone please help me correct the syntax and suggest how to structure the query to achieve the desired output?





Any help would be greatly appreciated.







Thank you!
gregbowerscpq