consolidate rows in sql not working as it should

Question

I am having this issue with making consolidate for rows, I have made the query, but the result aren't correct.

It may consolidate the rows but for some result it will divide them into two different rows , what i need is to make them all in one row only if the id is matching.

And here is the query :

Select  
    trxTransactionSaleItem.TransactionKey   
    , 'Sale' As TrxnType
    , InvProduct.Id 
    , InvProduct.UPC
    , trxTransactionSaleItem.Description
    , invproduct.Description2
    , invProduct.ProductGroupKey
    , sum (Quantity/ISNULL(UOMBaseQuantity,1)) as Quantity
    , Price
    , SUM(DiscountAmount) AS DA
    , SUM(SurchargeTotal) AS ST
    , sum (Total) as Total
    , ISNULL(UOM.Description,'') as UOM
From    
    trxTransactionSaleItem
INNER JOIN  
    InvProduct on trxTransactionSaleItem.ProductKey = InvProduct.ProductKey
LEFT JOIN 
    InvUOMGroupDetail UOMD on UOMGroupDetailKey = UOMD.UOMGroupDetailKey
LEFT JOIN 
    InvUOM UOM on UOMD.UOMKey = UOM.UOMKey
Where 
    Type = 0 
    And IsExchange = 0
    And trxTransactionSaleItem.TransactionKey = 60000000022537
group by 
    trxTransactionSaleItem.TransactionKey
    , InvProduct.Id
    , InvProduct.UPC
    , trxTransactionSaleItem.Description
    , invproduct.Description2
    , invProduct.ProductGroupKey
    , Quantity 
    , Price
    , DiscountAmount 
    , SurchargeTotal
    , Total
    , UOM.Description

So why its not coming in one row ?

Answer 1

Your group by should have only the fields that are not in aggregation functions. It should look like:

group by trxTransactionSaleItem.TransactionKey,
         InvProduct.Id,
         InvProduct.UPC,
         trxTransactionSaleItem.Description,
         invproduct.Description2,
         invProduct.ProductGroupKey,
         Price,
         ISNULL(UOM.Description, '')