SQLTeam.com | Weblogs | Forums

Grand total


#1

Hi I am just trying to get a total for a singe ID with other fields. So I took, just to experiment, I used
AdventureWorks2012 database. It has the SalesOrderID 4 times and the associated unit price with each id.
I just want, as results, the ID 43659 and the total 20565.6206
SELECT [SalesOrderID]
,[OrderQty]
,[ProductID]
,Sum([UnitPrice])
FROM [AdventureWorks2012].[Sales].[SalesOrderDetail]
WHERE [SalesOrderID] = 43659
Group BY [OrderQty]
,[ProductID]
,[LineTotal]
,[SalesOrderID]

Right now I get:
SalesOrderID OrderQty ProductID (No column name)
43659 1 716 28.8404
43659 1 771 2039.994
43659 1 772 2039.994
43659 1 774 2039.994
43659 1 776 2024.994
43659 1 778 2024.994
43659 2 712 5.1865
43659 2 773 2039.994
43659 3 714 28.8404
43659 3 777 2024.994
43659 4 711 20.1865
43659 6 709 5.70

How can I just get
43659 20565.6206

Thank you


#2

remove all ProductID references?


#3

Yeah, looks like that's the answer:

SELECT [SalesOrderID]
-- Not this one ,[OrderQty]
-- Not this one ,[ProductID]
,Sum([UnitPrice]) 
FROM [AdventureWorks2012].[Sales].[SalesOrderDetail]
WHERE [SalesOrderID] = 43659
Group BY
-- Not this one  [OrderQty]
-- Not this one ,[ProductID]
-- Not this one ,[LineTotal],
[SalesOrderID]

But I just wonder if there is maybe?? a need to SUM([UnitPrice] * [OrderQty]) rather than SUM of just [UnitPrice]??