So my predicament is the following:
I am trying to get the total of all of my orders (After discounts). But I can't even seem to get the sum of the order totals to add properly.
As per the screenshot, each order can have multiple line items (each row from orderdetails is a line item). These line items make a sub-total, on which a discount and taxes can be applied.
My forms all work fine for this, but I want a report of all my orders so I can track total sales.
Problem is my query just won't add them up. As per the attached, my query is the following:
But I get the result in the 2nd screenshot. I can get around this by making this query a subquery of another query, but I'd like to try and not have a million queries if I can help it. Can anyone offer me any advice, or is a suqquery my best option here?
Cheers!
I am trying to get the total of all of my orders (After discounts). But I can't even seem to get the sum of the order totals to add properly.
As per the screenshot, each order can have multiple line items (each row from orderdetails is a line item). These line items make a sub-total, on which a discount and taxes can be applied.
My forms all work fine for this, but I want a report of all my orders so I can track total sales.
Problem is my query just won't add them up. As per the attached, my query is the following:
Code:
SELECT OrderDetail.OrderID, OrderDetail.SKU, OrderDetail.Quantity, Products.UnitPrice, [Quantity]*[UnitPrice] AS ItemSubTotal, Sum([ItemSubTotal]) AS OrderSubTotal
FROM Products INNER JOIN OrderDetail ON Products.SKU = OrderDetail.SKU
GROUP BY OrderDetail.OrderID, OrderDetail.SKU, OrderDetail.Quantity, Products.UnitPrice, [Quantity]*[UnitPrice];
But I get the result in the 2nd screenshot. I can get around this by making this query a subquery of another query, but I'd like to try and not have a million queries if I can help it. Can anyone offer me any advice, or is a suqquery my best option here?
Cheers!