CODEWITHSUNDEEP
sql-server-2008
tsvsdev
tsvsdev

Reputation: 131

Adding columns resulting from GROUP BY clause

I got stuck in a scenario where I need to add columns from two rows after using GROUP BY. My query goes as:

select AcctId,DC,sum(TrnAmt) from TableId
group by AcctId,DC
order by AcctId,DC

Result:-

VcrAcctId      DrCr  SumTranAmt
51            C    37469
51      D      37000

My expected result is:

VcrAcctId      Actual
51     37469-37000

How can I get my expected result in the same query?

Upvotes: 4

Views: 14720

Answers (2)

Martin Smith
Martin Smith

Reputation: 453288

Based on discussion in the comments I think you need

SELECT AcctId,
       Sum(CASE DC
             WHEN 'C' THEN TrnAmt
             WHEN 'D' THEN -TrnAmt
             ELSE 0
           END) AS DC
FROM   TableId
GROUP  BY AcctId
ORDER  BY AcctId

If in fact you do want the credits and debits split out you can use

SELECT AcctId,
       Sum(CASE
             WHEN DC = 'C' THEN TrnAmt
             ELSE 0
           END) AS C,
       Sum(CASE
             WHEN DC = 'D' THEN TrnAmt
             ELSE 0
           END) AS D
FROM   TableId
GROUP  BY AcctId
ORDER  BY AcctId

You could cast the SUM expressions to varchar and concatenate them to get the results shown in the question if that is what is actually needed.

Upvotes: 1

cairnz
cairnz

Reputation: 3957

This is assuming Actual is ordered on DrCR, so that C result row comes before D result row. Also, replace Table1 with a CTE from your original query, so that you first sum up per VcrAcctId and DC.

SELECT VcrAcctId, STUFF((SELECT '-' + convert(varchar(100),SumTranAmt)
              FROM Table1 I
              WHERE Table1.VcrAcctId = I.VcrAcctID 
              ORDER BY DrCr
             FOR XML PATH ('')),1,1,'')
FROM Table1
Group by VcrAcctId

SQL Fiddle Demo

Upvotes: 1

Related Questions