I am using SQL Server Management Studio v18.

I have an original transaction that might get adjusted either by amount or by commission%.

I want a query to total up the transactions based on ID and company and I want to keep the original transaction's TransType.

I have full understanding on how to aggregate columns but I don't know how to keep the TransType.

Original table:

Final result:

CodePudding user response：

This query should give you the desired result set. (Solved here: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=aa617a6a403de147792544cdf97e9781)

SELECT 
summed_up.id, 
S1.transtype, 
summed_up.amount, 
summed_up.commission, 
summed_up.commission_percent, 
summed_up.company 
FROM
    (
    SELECT id, SUM(Amount) amount, SUM(Commission) commission, SUM(commission_percent) commission_percent, company
    FROM yourtable 
    GROUP BY id, company) AS summed_up
LEFT JOIN 
     (
     SELECT id, Transtype, company FROM yourtable WHERE notes = 'Original Transaction'
     ) AS S1 
     ON S1.id = summed_up.id AND S1.company = summed_up.company

You are essentially separating the summing operation, and the Original Transaction retrieval operation. Finally putting them together. Let me know if you have any queries.

CodePudding user response：

You can simply aggregate and use a correlated subquery for the TranType column:

select ID, 
  (
    select transtype from t t2 
    where t.id = t2.id and t.company = t2.company and t2.notes = 'Original Transaction' 
  ) TransType,
  Sum(Amount) as Amount, Sum(Commission) as Commission,
  Sum([Commission%]) as [Commission%], Company
from t
group by ID, Company;