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 ?

At present you're grouping by lots of fields; are they all fields you actually want to group by, or did you just add additional ones to get the query to run? That could be your issue - only group by what you need to group by, then use aggregate functions (e.g `SUM`, `AVG`, `MAX`) on any remaining columns you wish to output but not group by. Alternatively, if you can describe your requirement we can better assist in fixing the code. — JohnLBevan, Feb 15 '15 at 14:17
Thanks for the info , you are correct , I haven't pay attention for this , thanks a lot :) — Mohammed Mustafa, Feb 15 '15 at 14:37

score 1 · Accepted Answer · answered Feb 15 '15 at 14:24

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, '')

consolidate rows in sql not working as it should

1 Answers1