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 (https://www.igmguru.com/salesforce/salesforce-cpq-training/)

[arnelgp]

replace the Extra comma (,) after As TotalQuantity with Space and run it again.

[June7]

What does "-- Additional aggregation and grouping logic" mean - more calculated fields?

"total quantity ordered overall" means total for all products in given range? You want this total shown on every record? Think have to build query for product aggregation, a query for total aggregation, then OUTER JOIN those queries. Each of those queries will need same filter criteria.

You are building this query in SSMS? What are you using for GUI - Access? Consider building a report and doing Sorting & Grouping and aggregate calcs there.

[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.