CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Dantuzzo
Dantuzzo

Reputation: 271

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.

Upvotes: 1

Views: 287

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270593

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.

Upvotes: 1

Related Questions