title: Sql Server sum multiple transactions done by a single transactionID
body: <p>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.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I get the following result:</p>
  
  <pre><code>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.
  </code></pre>