How to find row wise total for SQL Server table

Question

I have a Table having following records.

---------------------------------------------------------
|VoucherDetailId    | VoucherId |   AccountId | Amount  |
---------------------------------------------------------
|    2              |    1      |         2   |  -5000  |
|    3              |    2      |         2   |  2000   |
|    15             |    8      |         2   |  777    |
---------------------------------------------------------

I want a query for below output..

------------------------------------------
| AccountId | Debit  | Credit  | Balance |
------------------------------------------
|   2       |   0    | 5000    | (5000)  |
|   2       |  2000  |   0     | (3000)  |
|   2       |  777   |   0     | (2223)  |
------------------------------------------

Thanks in Advance...

I want a ruby encrusted pony. Suggest you try to make an attempt... — Mitch Wheat, May 08 '13 at 07:22
And what is the SQL query you have used, which is not working? — Raj, May 08 '13 at 07:23
You need a running total. Much easier in 2012 than previous versions. — Martin Smith, May 08 '13 at 07:26
possible duplicate of [Calculate a Running Total in SqlServer](http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver) — Martin Smith, May 08 '13 at 07:28
Check this : http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver — Ravi Singh, May 08 '13 at 07:37

score 3 · Accepted Answer · answered May 08 '13 at 07:32

Try this :-

Select AccountID,
       case when SIGN(amount) = 1 then 
        Amount
       ELSE
        0 
      END as Debit,
       case when SIGN(amount) = -1 then 
            abs(Amount)
       ELSE
       0 
      END as Credit,
      (SELECT abs(SUM(Amount) )
    FROM Sample b 
    WHERE b.VoucherDetailId <= a.VoucherDetailId) AS balance 
 from Sample a

Results :-

  ╔═══════════╦═══════╦════════╦═════════╗
  ║ ACCOUNTID ║ DEBIT ║ CREDIT ║ BALANCE ║
  ╠═══════════╬═══════╬════════╬═════════╣
  ║         2 ║     0 ║   5000 ║    5000 ║
  ║         2 ║  2000 ║      0 ║    3000 ║
  ║         2 ║   777 ║      0 ║    2223 ║
  ╚═══════════╩═══════╩════════╩═════════╝

Demo in SQL FIDDLE

How to find row wise total for SQL Server table

1 Answers1