need sum of value based on category

Question

Table have data like

<--------------------------->  
no      category     value 
<--------------------------->      
1        1           2000    
2        1           1000     
3        2            500    
4        3           3000    
5        1           2000   
6        2           -500        
7        3           5000    
8        1          -1000

And i want output like

<----------------------------------> 
no  category     amount       Sum   
<---------------------------------->   
1        1       2000         2000                
2        1       1000         3000                 
3        2        500          500                 
4        3       3000         3000                 
5        1       2000         5000                  
6        2       -500            0                 
7        3       5000         8000
8        1      -1000         4000

for eg: take category=1 so,

no:1
amount=2000--->total=2000
no:2
amount=1000---->total=3000
no:5
amount=2000---->total=5000
no:8
amount=-1000---->total=4000

that means sum of each category on every row and with better performance

[This article explains how to calculate running total](http://www.sqlperformance.com/2012/07/t-sql-queries/running-totals). — Nikola Markovinović, Jul 30 '13 at 11:10

score 0 · Answer 1 · answered Jul 30 '13 at 11:08

0

You need to self-join both tables with row numbers:

;WITH t(No,Category,value,row) AS
(
    SELECT No, category, value
      ,ROW_NUMBER() OVER (Partition by category ORDER BY no) AS row
   FROM Table1
)
SELECT t1.No, t1.category, t1.value, sum(t2.value) as sums
  FROM t t1
  JOIN t t2
    ON t1.row >= t2.row
   AND t1.Category = t2.Category
 GROUP BY t1.No, t1.category, t1.value;

Output:

| NO | CATEGORY | VALUE | SUMS |
--------------------------------
|  1 |        1 |  2000 | 2000 |
|  2 |        1 |  1000 | 3000 |
|  3 |        2 |   500 |  500 |
|  4 |        3 |  3000 | 3000 |
|  5 |        1 |  2000 | 5000 |
|  6 |        2 |  -500 |    0 |
|  7 |        3 |  5000 | 8000 |
|  8 |        1 | -1000 | 4000 |

See this SQLFiddle

answered Jul 30 '13 at 11:08

Himanshu

31,810
31
111
133

its take `00:00:02` execution time....its really slow @himso56 – user2633770 Jul 30 '13 at 11:32
@user2633770 - I don't think there should be any faster way than this. Looking at [the Fiddle](http://sqlfiddle.com/#!3/abc4d/20), it takes about 1 to 3 ms only. – Himanshu Jul 30 '13 at 11:36
i have 8000 and counting records – user2633770 Jul 30 '13 at 11:42

score 0 · Accepted Answer · edited May 23 '17 at 10:31

0

What you need is calculating rolling total. The fastest way in SQL Server 2005 / 2008 / 2008 R2 I know is described here: https://stackoverflow.com/a/13744550/1744834. You have to have unique secuential column inside each category to use this method, like this:

create table #t (no int, category int, value int, id int, primary key (category, id))

insert into #t (no, category, value, id)
select no, category, value, row_number() over (partition by category order by no)
from test

;with 
CTE_RunningTotal
as
(
    select T.no, T.category, T.value, cast(T.value as decimal(29, 10)) as total_value, T.id
    from #t as T
    where T.id = 1
    union all
    select T.no, T.category, T.value, cast(T.value + C.total_value as decimal(29, 10)) as total_value, T.id
    from CTE_RunningTotal as C
        inner join #t as T on T.id = C.id + 1 and T.category = C.category
)
select C.no, C.category, C.value, C.value
from CTE_RunningTotal as C
option (maxrecursion 0)

SQL FIDDLE EXAMPLE

you could also use shorter query, but performance is worse (I think it's O(N^2) vs O(N) for recursive CTE):

select t1.no, t1.category, t1.value, sum(t2.value)
from test as t1
    inner join test as t2 on t2.category = t1.category and t2.no <= t1.no
group by t1.no, t1.category, t1.value
order by t1.no

SQL FIDDLE EXAMPLE

edited May 23 '17 at 10:31

Community

1
1

answered Jul 30 '13 at 11:27

Roman Pekar

107,110
28
195
197

this working in sql server 2005? @RomanPekar – user2633770 Jul 30 '13 at 11:41
yes it should work in SQL Server 2005 – Roman Pekar Jul 30 '13 at 11:43
`Types don't match between the anchor and the recursive part in column "total_value" of recursive query "CTE_RunningTotal"` this is the error i got – user2633770 Jul 30 '13 at 11:44
you probable have decimal type for value column? – Roman Pekar Jul 30 '13 at 11:46
why the above code not work with decimal value? – user2633770 Jul 30 '13 at 11:47
see http://msdn.microsoft.com/en-us/library/ms190476.aspx - type of sum could be not equal type of numbers you're adding together – Roman Pekar Jul 30 '13 at 11:51

score -1 · Answer 3 · answered Jul 30 '13 at 10:57

-1

You could use the following sql functions SQL functions it shows you how to use it and how to apply it

answered Jul 30 '13 at 10:57

no name

7
7

This should be a comment. Also the OP wants it for SQL Server not for MySQL. – Himanshu Jul 30 '13 at 11:17

need sum of value based on category

3 Answers3

See this SQLFiddle