Using Sum function

Question

I have table with following structure:

TestTable (ID INT, LoanID INT, Amount INT)

ID  LoanID  Amount
1   1        20
2   1        30
3   1        15
4   2        40
5   2        20
6   3        50

I want to calculate LoanAmounts with this scenario

ID  LoanID  Amount  Sum
1   1       20      20
2   1       30      50
3   1       15      65
4   2       40      40
5   2       20      60
6   3       50      50

What is the best way to get this Sum collumn (Of course without cursor :))

score 3 · Accepted Answer · answered Nov 05 '14 at 13:31

You can use a correlated sub-query to get the running-totals:

SELECT t.ID, t.LoanID, t.Amount,
       [Sum] = (SELECT SUM(AMOUNT)
                FROM TestTable t2
                WHERE t.LoanID=t2.LoanID
                AND t2.ID <= t.ID)
FROM TestTable t

Demo

D Stanley · Answer 2 · 2014-11-05T13:35:44.860

2

Unfortunately SQL Server 2008 doesn't have a clean function to do a cumulative sum, so you need to add up all of the "previous" rows with the same LoadID:

SELECT
  ID,
  LoanID,  
  Amount,
  (SELECT SUM(Amount) FROM TestTable t2 WHERE t2.LoanID = t.LoanID AND t2.ID <= t.ID) Sum
FROM TestTable t

edited Nov 05 '14 at 13:35

answered Nov 05 '14 at 13:30

D Stanley

149,601
11
178
240

It is not cumulative sum, look at OP output, Id 4 has 40 as sum and id 3 as 65 – radar Nov 05 '14 at 13:38
1

@RADAR It is cumulative _per `LoanID` group_. – D Stanley Nov 05 '14 at 13:39
@D Stanley, your query is good +1 – radar Nov 05 '14 at 13:40

score 0 · Answer 3 · answered Nov 05 '14 at 13:33

0

select ID, LoanID, Amount, 
       (select sum(Amount)
        from TestTable A
        where  A.LoanID = B.LoanID and
               A.ID <= B.ID
       ) as Sum
from TestTable B;

answered Nov 05 '14 at 13:33

Spider

367
3
15

Mladen Uzelac · Answer 4 · 2014-11-05T13:58:46.467

0

By using windowing function:

select *, sum(amount) over (partition by loanid order by id) as "sum" from testtable;

SQL server 2012: http://sqlfiddle.com/#!6/dc108/8

SQL server 2008: http://sqlfiddle.com/#!3/dc108/10

It works on SQL server 2012, but not on sql server 2008 using sqlfiddle.

edited Nov 05 '14 at 13:58

answered Nov 05 '14 at 13:39

Mladen Uzelac

1,173
1
10
14

score 0 · Answer 5 · answered Nov 06 '14 at 06:58

0

Try this

  SELECT b.id,a.LoanID,b.Amount,
           Sum(a.Amount) AS [Sum]
    FROM   #temp_table a
    JOIN   #temp_table b ON a.id <= b.id
                  AND a.LoanID = b.LoanID
    GROUP  BY b.id,a.LoanID,b.Amount

answered Nov 06 '14 at 06:58

PP006

681
7
17

score -3 · Answer 6 · answered Nov 05 '14 at 13:28

-3

SELECT SUM(column_name) FROM table_name;

answered Nov 05 '14 at 13:28

Klyse

59
2
11

Using Sum function

6 Answers6