Cumulative Sum in sql query

Question

I have query (below) that show me data in the column "Monthly".

Result

but I need to rewrite this query to cumulative sums as shows third column on picture. How can I do it? Thanks for any reply.

My current query:

select cast( ( Prodej.Ce_Jedn * Prodej.Mnoz ) / 1000 as numeric(15,2) ) as "Monthly" ,
       YEAR(  FAV.vatDate ) as "Rok" ,
       month( FAV.VatDate ) as "Měsíc" 
from Prodej
join FAKTVYDA FAV on FAV.Ci   = PRODEJ.C_Fak
                 and FAV.Rada = PRODEJ.R_Fak
where YEAR(FAV.VATDate) > year(getdate())-3
  and FAV.Rada in ('10','20','30','60') 
  and PRODEJ.C_Fak <> 0
  and '@{Stredisko.ParameterValue}' = case Prodej.Str 
                                        when ''  then FAV.Str
                                        when '-' then FAV.Str 
                                        else     Prodej.str
                                      end
order by month(FAV.VatDate)

http://www.sqlperformance.com/2012/07/t-sql-queries/running-totals — Aaron Bertrand, Oct 12 '13 at 01:01
possible duplicate of [Calculate running total / running balance](http://stackoverflow.com/questions/11310877/calculate-running-total-running-balance) — Aaron Bertrand, Oct 12 '13 at 01:05
See this CodeProject article: http://www.codeproject.com/Articles/300785/Calculating-simple-running-totals-in-SQL-Server There's more than one way to do it. — Nicholas Carey, Oct 12 '13 at 01:06

score 0 · Answer 1 · answered May 09 '14 at 06:47

0

When you use a SUM-statement, the query also requires a GROUP BY-statement. This is not included in the query above, so it won't work.

answered May 09 '14 at 06:47

Ron

1

score -1 · Answer 2 · edited May 23 '17 at 11:53

-1

You can achieve that by using a SUM statement in front of the field you want summarized. Please see this post which provides a solution that should fit your problem.

select cast( ( Prodej.Ce_Jedn * Prodej.Mnoz ) / 1000 as numeric(15,2) ) as "Monthly" ,
YEAR(  FAV.vatDate ) as "Rok" ,
month( FAV.VatDate ) as "Měsíc" 
SUM(( Prodej.Ce_Jedn * Prodej.Mnoz ) / 1000 as numeric(15,2) ) as "Cumulative"
from Prodej
join FAKTVYDA FAV on FAV.Ci   = PRODEJ.C_Fak
and FAV.Rada = PRODEJ.R_Fak
where YEAR(FAV.VATDate) > year(getdate())-3
and FAV.Rada in ('10','20','30','60') 
and PRODEJ.C_Fak <> 0
and '@{Stredisko.ParameterValue}' = case Prodej.Str 
when ''  then FAV.Str
when '-' then FAV.Str 
else     Prodej.str
end
order by month(FAV.VatDate)

Hope that helps,

Chris

edited May 23 '17 at 11:53

Community

1
1

answered Oct 12 '13 at 00:58

campagnolo_1

2,710
1
17
25

Possibly because it resembles a link-only answer? (I didn't down vote you.) You may wish to [edit] your answer and add some additional details. Just a thought. – Dan Pichelman Oct 12 '13 at 01:14
Pretty lame in my book, none of the other answers are any different. – campagnolo_1 Oct 12 '13 at 01:37
Maybe be a member for longer than 10 days before deciding whether something here is lame or not. Please read [this discussion](http://meta.stackexchange.com/questions/8231/are-answers-that-just-contain-links-elsewhere-really-good-answers) to get at least some background on why a link to another web site or a low-quality duplicate is not desirable. – Aaron Bertrand Oct 12 '13 at 02:41

Cumulative Sum in sql query

2 Answers2