March 5, 2015 at 10:51 am
hoping someone can help. I have to create a query that will display average price average quantity and product sales for an order. Product sales is a calculated field UnitPrice*Quantity*(1-discount) there should only be one row. i tried it this way but its not one row of data
SELECT AVG(UnitPrice) AS [Average Unit Price], AVG(Quantity) AS [Average Quantity], (UnitPrice * Quantity) * (1 - Discount) AS [Product Sales]
FROM dbo.[Order Details]
GROUP BY (UnitPrice * Quantity) * (1 - Discount)
March 5, 2015 at 10:59 am
I suppose this is what you want:
SELECT AVG(UnitPrice) AS [Average Unit Price], AVG(Quantity) AS [Average Quantity], SUM((UnitPrice * Quantity) * (1 - Discount)) AS [Product Sales]
FROM dbo.[Order Details]
GROUP BY (UnitPrice * Quantity) * (1 - Discount)
-- Gianluca Sartori
March 5, 2015 at 11:14 am
thanks, its almost what i was looking for but i need all input to be accross one row ill like the first example
and not like second
March 5, 2015 at 11:17 am
My bad, I forgot to delete the last line
SELECT AVG(UnitPrice) AS [Average Unit Price], AVG(Quantity) AS [Average Quantity], SUM((UnitPrice * Quantity) * (1 - Discount)) AS [Product Sales]
FROM dbo.[Order Details]
Better?
-- Gianluca Sartori
March 5, 2015 at 11:31 am
thank you so much you saved my life lol
March 5, 2015 at 11:41 am
Glad I could help
-- Gianluca Sartori
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply