Adding columns resulting from GROUP BY clause

Question

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?

Are there always exactly two rows per `VcrAcctId`? If not what should be shown? — Martin Smith, Dec 15 '12 at 12:57
NO, there can be only one VcrAcctId, in that case DrCr column will be either D or C with one SumTranAmt — tsvsdev, Dec 15 '12 at 13:01
So you actually always want to show `C-D` if there are two rows and what if there is just one? Is it differentiated in the result if it is a `C` or a `D`? Are you in fact trying to get the net value of all credits and debits so that ideally you would want the result `469` as the result for your example data? — Martin Smith, Dec 15 '12 at 13:05
exactly, i have a transaction table in which an account can have multiple Credits or Debits or only single credit or debit. — tsvsdev, Dec 15 '12 at 13:13

score 1 · Answer 1 · answered Dec 15 '12 at 12:56

1

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

answered Dec 15 '12 at 12:56

cairnz

3,917
1
18
21

my query is Resulting from a CTE query, I don't know whether consecutive CTE querry works or not. – tsvsdev Dec 15 '12 at 13:26
Performance will be worse though, the `XML PATH` subquery referencing the CTE will likely cause multiple evaluations of the underlying `group by` query that defines the CTE. – Martin Smith Dec 15 '12 at 13:45

score 1 · Accepted Answer · answered Dec 15 '12 at 13:15

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.

hi Martin..can you give a solution to my link http://stackoverflow.com/questions/13899909/calculating-from-rows-and-columns.. — tsvsdev, Dec 16 '12 at 09:49

Adding columns resulting from GROUP BY clause

2 Answers2

Linked