Dantuzzo Dantuzzo - 23 days ago 12
SQL Question

Sql Server sum multiple transactions done by a single transactionID

I'm having a problem with a query that shows duplicate rows for the primary key (TransactionID), this is taken directly from adventureWorks database. Basically there are several same transactionID's for a given transaction because they bought more then 1 thing, I'm trying to show only one of those and sum the total due but I can't seem to do it.

SELECT
AdventureWorks2014.Sales.SalesOrderHeader.SalesOrderID AS TransactionID,
AdventureWorks2014.Sales.SalesOrderHeader.OrderDate,
AdventureWorks2014.Sales.SalesOrderDetail.ProductID,
AdventureWorks2014.Sales.SalesOrderHeader.TerritoryID,
AdventureWorks2014.Sales.SalesOrderDetail.UnitPrice,
AdventureWorks2014.Sales.SalesOrderDetail.OrderQty,
SUM(AdventureWorks2014.Sales.SalesOrderDetail.UnitPrice * AdventureWorks2014.Sales.SalesOrderDetail.OrderQty) AS Total
FROM AdventureWorks2014.Sales.SalesOrderHeader
INNER JOIN AdventureWorks2014.Sales.SalesOrderDetail
ON AdventureWorks2014.Sales.SalesOrderHeader.SalesOrderID = AdventureWorks2014.Sales.SalesOrderDetail.SalesOrderID
GROUP BY AdventureWorks2014.Sales.SalesOrderHeader.SalesOrderID,
AdventureWorks2014.Sales.SalesOrderHeader.OrderDate,
AdventureWorks2014.Sales.SalesOrderDetail.ProductID,
AdventureWorks2014.Sales.SalesOrderHeader.TerritoryID,
AdventureWorks2014.Sales.SalesOrderDetail.UnitPrice,
AdventureWorks2014.Sales.SalesOrderDetail.OrderQty
ORDER BY SalesOrderHeader.SalesOrderID ASC


I get the following result:

43659 2011-05-31 00:00:00.000 712 5 5,1865 2 10,373
43659 2011-05-31 00:00:00.000 709 5 5,70 6 34,20
43659 2011-05-31 00:00:00.000 771 5 2039,994 1 2039,994
etc.

Answer

If you only want to show one transaction, that should be all you have in your GROUP BY:

SELECT soh.SalesOrderID AS TransactionID, 
       SUM(sod.UnitPrice * sod.OrderQty) AS Total
FROM AdventureWorks2014.Sales.SalesOrderHeader soh INNER JOIN
     AdventureWorks2014.Sales.SalesOrderDetail sod
     ON soh.SalesOrderID = sod.SalesOrderID 
GROUP BY soh.SalesOrderID
ORDER BY soh.SalesOrderID ASC;

You can add in additional columns from the header but not from the detail record -- in both the SELECT and the GROUP BY.

Notice that table aliases make the query easier to write and to read.