how to sum tow field at get inline result?

Question

i have tow field for example credit an debit in one table. and i need to sum them and get result at each line for example :

date          debit   credit  amount
2015/01/01     20       0        20
2015/01/02      0       5        15
2015/01/03      0       30      -15

i hope you help me to get the amount by a query thanks

This should answer your question: http://stackoverflow.com/questions/2120544/how-to-get-cumulative-sum — fafl, Apr 21 '16 at 14:49

Tim Schmelter · Accepted Answer · 2016-04-21T15:03:19.237

0

With SQL-Server 2012 or newer you can use this:

SELECT [date], debit, credit, amount,
   SUM(debit-credit) OVER(ORDER BY [date] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS amount
FROM TableName
ORDER BY [date]

Read: OVER-clause, especially the ROWS | RANGE part

With other versions you have to use a correlated subquery:

SELECT [date], debit, credit, amount,
  (SELECT SUM(debit-credit) 
   FROM TableName t2
   WHERE [date] <= t1.[date]) AS amount
FROM TableName t1
ORDER BY [date]

edited Apr 21 '16 at 15:03

answered Apr 21 '16 at 14:57

Tim Schmelter

450,073
74
686
939

this code help me very well.but i have another problem how can i filter date and get true result for example i filter date from 2015/01/02 and i want get the result -15 in my amount could you help me whit this? – Iman Farhoumand Apr 22 '16 at 19:06

score 0 · Answer 2 · answered Apr 21 '16 at 15:02

I agree with Tim's answer, I added some extra lines:

declare @credit as table (
    [date] datetime,
    amount int
)

declare @debit as table (
    [date] datetime,
    amount int
)

insert into @debit values 
    ('2015-01-01', 20)

insert into @credit values 
    ('2015-01-02', 5),
    ('2015-01-03', 30)


select
    [date], debit, credit, SUM(debit-credit) OVER(ORDER BY [date] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS amount
from(
    select 
        [date], sum(debit) debit, sum(credit) credit
    from
    (
    select 
        [date], 0 credit, d.amount debit
    from
        @debit d
    union all
    select 
        [date], c.amount credit, 0 debit
    from
        @credit c
    ) j group by j.date
) x

how to sum tow field at get inline result?

2 Answers2