We help IT Professionals succeed at work.

SQL Cumulative

felipesch
felipesch asked
on
Medium Priority
653 Views
Last Modified: 2008-02-01
I have a query that return this:

DATA                        CASOS_CRIADOS    CASOS_ENCERRADOS       A-B  
01/03/2006      53                    42                          11
02/03/2006      100                     55                          45
03/03/2006      107                    91                          16
04/03/2006      89                    17                          72
...

What can I create a collum named "A-B-CUMULATIVE", thats represents the cumulative values from collum CRIADOS_MINUS_ENCERRADOS? The result is this:

DATA                        CASOS_CRIADOS    CASOS_ENCERRADOS       A-B     A-B-CUMULATIVE
01/03/2006      53                    42                          11      11          
02/03/2006      100                     55                          45      65                   <-- THE LAST A-B + ACTUAL
03/03/2006      107                    91                          16      81                   <-- THE LAST A-B + ACTUAL
04/03/2006      89                    17                          72      153                 <-- THE LAST A-B + ACTUAL
...

Thanks in advance,

Felipe.

Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2010

Commented:
Hi felipesch,

Something like this should work:

SELECT t.DATA, (t.CASOS_CRIADOS + t.CASOS_ENCERRADOS) AS TheSum,
    t.CASOS_CRIADOS, t.CASOS_ENCERRADOS, (SELECT SUM(tt.CASOS_ENCERRADOS)
    FROM YourTable tt WHERE tt.DATA = t.DATA) AS RunningTot
FROM YourTable t
ORDER BY t.DATA

Regards,

Patrick
I have no idea what actual is - the numbers don't seem to m,ake much sense - but you need a cursor to do this (or some other looping alternative). You will also need to explicitly enforce your order with an ORDER BY statement as there is no such thing as Last in an RDBMS.
CERTIFIED EXPERT
Top Expert 2010

Commented:
felipesch,

Sorry, had a typo:

SELECT t.DATA, (t.CASOS_CRIADOS + t.CASOS_ENCERRADOS) AS TheSum,
    t.CASOS_CRIADOS, t.CASOS_ENCERRADOS, (SELECT SUM(tt.CASOS_ENCERRADOS)
    FROM YourTable tt WHERE tt.DATA <= t.DATA) AS RunningTot
FROM YourTable t
ORDER BY t.DATA


Regards,

Patrick
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
I think matthewspatrick's query needs some adjustments:


SELECT DATA, CASOS_CRIADOS, CASOS_ENCERRADOS,
    (CASOS_CRIADOS - CASOS_ENCERRADOS) AS [A-B],
    (SELECT SUM(CASOS_CRIADOS - CASOS_ENCERRADOS)
    FROM YourTable tt WHERE tt.DATA <= t.DATA) AS [A-B-Cumulative]
FROM YourTable t
ORDER BY DATA

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT
Top Expert 2010

Commented:
Scott,

> I think matthewspatrick's query needs some adjustments:

Almost certainly.  I had trouble following what the various columns were; I focused my energies on getting
the subquery to run the cumulative totals :)

Patrick
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Actually that was one of the adjustments :-)
CERTIFIED EXPERT
Top Expert 2010

Commented:
Indeed :)
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.