CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-bywindow-functions
Carlos Ferreira
Carlos Ferreira

Reputation: 93

Add a cumulative/running total column

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.

Upvotes: 0

Views: 80

Answers (2)

zip
zip

Reputation: 4061

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

Upvotes: 0

GMB
GMB

Reputation: 222412

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

Upvotes: 1

Related Questions