Add a cumulative/running total column

Question

I'am trying to do a running total (without success) over a calculated column on sql.

My original code:

select t.codigo, t.nome, t.total  
from(
select codigo, nome,  SUM(CASE When ANO = 2018 Then VLCOMPRA Else 0 End ) as total
from clientes
left join ACUMTERC on ACUMTERC.TPTERC = 2 and TPOPER = 2 AND ACUMTERC.TERCEIRO = CLIENTES.CODIGO
group by codigo, nome) as t   
ORDER BY total DESC

What i tried:

select t.codigo, t.nome, t.total, SUM(t.total) OVER(PARTITION BY t.codigo ORDER BY t.codigo) RunningTotal    
from( select codigo, nome, SUM(CASE When ANO = 2018 Then VLCOMPRA Else 0 End ) as total from clientes left join ACUMTERC on ACUMTERC.TPTERC = 2 and TPOPER = 2 AND ACUMTERC.TERCEIRO = CLIENTES.CODIGO group by codigo, nome) as t   
ORDER BY total DESC

My result:

codigo | nome  | total | Running total
-------+-------+-------+---------------
000001 | name1 |   300 !   300
000003 | name3 |   200 |   200
000002 | name2 |   100 |   100

What i need:

codigo | nome  | total | Running total
-------+-------+-------+---------------
000001 | name1 |   300 !   300
000003 | name3 |   200 |   500
000002 | name2 |   100 |   600

Any help? Thank you.

GMB · Accepted Answer · 2020-02-13T16:03:00.423

1

I strongly suspect that your query can be simplified:

to start with, I don't understand why you need to do a conditional sum: you could just filter in the join
there is no need for a subquery at all

When it comes to your requirement, you can do a window sum().

I would also strongly suggest to prefix each column with the table it belongs to: this makes the query unambiguous about the data structures; I made a few assumptions that you might want to check (I also used table aliases to shorten the query).

Consider:

select 
    c.codigo, 
    c.nome,  
    coalesce(sum(a.vlcompra), 0) as total,
    sum(coalesce(sum(a.vlcompra), 0)) 
        over(order by coalesce(sum(a.vlcompra), 0) desc) running_total
from clientes c
left join acumterc a 
    on  a.tpterc = 2 
    and a.tpoper = 2 
    and a.terceiro = c.codigo
    and a.ano = 2018
group by c.codigo, c.nome

edited Feb 13 '20 at 16:03

answered Feb 13 '20 at 15:42

GMB

216,147
25
84
135

vlcompra does not exist in table clientes, it is calculated Getting error: Msg 207, Level 16, State 1, Line 4 Invalid column name 'vlcompra'. Msg 207, Level 16, State 1, Line 4 Invalid column name 'vlcompra'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'vlcompra'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'vlcompra'. Msg 207, Level 16, State 1, Line 5 Invalid column name 'vlcompra'. Msg 207, Level 16, State 1, Line 5 Invalid column name 'vlcompra'. – Carlos Ferreira Feb 13 '20 at 16:00
"to start with, I don't understand why you need to do a conditional sum: you could just filter in the join there is no need for a subquery at all" Because i do not have much experience/knowledge on SQL But i learn a lot with you guys. Thank you so much – Carlos Ferreira Feb 13 '20 at 16:10

zip · Answer 2 · 2020-02-13T16:24:07.803

0

You can do it this way, using ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT:

select t.codigo, t.nome, t.total, 
SUM(t.total) OVER(ORDER BY t.codigo ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) RunningTotal

from( select codigo, nome, SUM(CASE When ANO = 2018 Then VLCOMPRA Else 0 End ) as total from clientes left join ACUMTERC on ACUMTERC.TPTERC = 2 and TPOPER = 2 AND ACUMTERC.TERCEIRO = CLIENTES.CODIGO group by codigo, nome) as t

    ORDER BY total DESC

An example with data:

 with tbl as
(
select '000001' as codigo , 'name1' as nome,   300 as total,  300 as Running_total
union select '000003' , 'name3' ,   200 ,   200
union select '000002' , 'name2' ,   100 ,   100
)

select t.codigo, t.nome, t.total, 
SUM(t.total) OVER(ORDER BY t.codigo asc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) RunningTotal

from tbl as t
ORDER BY codigo asc
  with tbl as
(
select '000001' as codigo , 'name1' as nome,   300 as total,  300 as Running_total
union select '000003' , 'name3' ,   200 ,   200
union select '000002' , 'name2' ,   100 ,   100
)

select t.codigo, t.nome, t.total, 
SUM(t.total) OVER(ORDER BY t.codigo asc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) RunningTotal

from tbl as t
ORDER BY codigo asc

Output:

codigo  nome    total   RunningTotal
000001  name1     300   300
000002  name2     100   400
000003  name3     200   600

edited Feb 13 '20 at 16:24

answered Feb 13 '20 at 15:36

zip

3,938
2
11
19

Getting error: Msg 102, Level 15, State 1, Line 2 Incorrect syntax near ')'. Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'as'. – Carlos Ferreira Feb 13 '20 at 15:57
I corrected it, was missing "ROW" affter CURRENT. let me know – zip Feb 13 '20 at 16:08
Sorry, same results – Carlos Ferreira Feb 13 '20 at 16:13
Carlos, any chance you can post the result of: ****** select codigo, nome, ANO ,VLCOMPRA from clientes left join ACUMTERC on ACUMTERC.TPTERC = 2 and TPOPER = 2 AND ACUMTERC.TERCEIRO = CLIENTES.CODIGO ****** so we can use it as test data please – zip Feb 13 '20 at 16:16
Carlos, I added in my answer an example with some data. You can test it to see that it does the running total. That is the same concept I used so suggest a solution to your problem – zip Feb 13 '20 at 16:25
it is too big to post here, but something like this: 10233 Confeccoes Josal, Lda. 2017 686.900000 10233 Confeccoes Josal, Lda. 2018 292.930000 10233 Confeccoes Josal, Lda. 2018 392.740000 10233 Confeccoes Josal, Lda. 2019 305.670000 10257 Daisy Fabrics Industria De Bordados, Lda 1991 -102.710000 10307 Larmoderno Ind. Cmercio Texteis, Lda. NULL NULL 10308 Donomo - Comercio Ind. Texteis, Sa. 1991 -115.550000 10308 Donomo - Comercio Ind. Texteis, Sa. 1991 -108.210000 10308 Donomo - Comercio Ind. Texteis, Sa. 1991 -87.040000 – Carlos Ferreira Feb 13 '20 at 16:29
Just put it in your question, at the end Carlos – zip Feb 13 '20 at 16:36
Msg 319, Level 15, State 1, Line 13 Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. – Carlos Ferreira Feb 13 '20 at 16:43

Add a cumulative/running total column

2 Answers2

Linked