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.

Answer 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