Home » SQL & PL/SQL » SQL & PL/SQL » Running Total
Running Total [message #299367] Mon, 11 February 2008 13:34 Go to next message
shekar2903
Messages: 1
Registered: February 2008
Junior Member
How to make running total of a column reset to zero when running total comes across a zero value in the same column?

I need to form a SQL Query based on the above criteria..?
Re: Running Total [message #299373 is a reply to message #299367] Mon, 11 February 2008 13:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a test case: create table and insert statements.
Also post the result you want with these data.
Always post your Oracle version (4 decimals).

Read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Running Total [message #299461 is a reply to message #299367] Tue, 12 February 2008 02:23 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
shekar2903 wrote on Mon, 11 February 2008 20:34
How to make running total of a column reset to zero when running total comes across a zero value in the same column?
When you define something like a running total you need something to order your set. Michel's reply was my initial response too: without some sample data we cannot help you. But I'm in a good mood today, so I've created a small example. Here's my table MHE_FOO (output reformatted):
 Name       Null?    Type
 ---------- -------- --------------
 ORDER_COL           NUMBER
 VAL_COL             NUMBER
I want the running total for val_col, sorted by order_col, and I want to reset this total whenever a 0 (zero) comes across. First, we need to identify groups to reset. I used the SUM() analytic function in combination with DECODE. Once I've got the groups I can use the SUM() analytic function again and use the groups I've identified as partition key.

My script looks like this:
CREATE TABLE mhe_foo(order_col NUMBER, val_col NUMBER)
/
INSERT INTO mhe_foo
VALUES      (1, 1);
INSERT INTO mhe_foo
VALUES      (2, 2);
INSERT INTO mhe_foo
VALUES      (3, 3);
INSERT INTO mhe_foo
VALUES      (4, 4);
INSERT INTO mhe_foo
VALUES      (5, 5);
INSERT INTO mhe_foo
VALUES      (6, 7);
INSERT INTO mhe_foo
VALUES      (7, 8);
INSERT INTO mhe_foo
VALUES      (8, 0);
INSERT INTO mhe_foo
VALUES      (9, 1);
INSERT INTO mhe_foo
VALUES      (10, 4);
INSERT INTO mhe_foo
VALUES      (11, 7);
INSERT INTO mhe_foo
VALUES      (12, 2);
INSERT INTO mhe_foo
VALUES      (13, 5);
INSERT INTO mhe_foo
VALUES      (14, 0);
INSERT INTO mhe_foo
VALUES      (15, 5);
INSERT INTO mhe_foo
VALUES      (16, 6);
INSERT INTO mhe_foo
VALUES      (17, 9);
INSERT INTO mhe_foo
VALUES      (18, 7);
INSERT INTO mhe_foo
VALUES      (19, 8);
INSERT INTO mhe_foo
VALUES      (20, 0);
INSERT INTO mhe_foo
VALUES      (21, 1);
INSERT INTO mhe_foo
VALUES      (22, 0);
INSERT INTO mhe_foo
VALUES      (23, 1);
INSERT INTO mhe_foo
VALUES      (24, 4);

SELECT   order_col
       , SUM(val_col) 
           OVER ( PARTITION BY the_group ORDER BY order_col) my_sum
FROM     (SELECT   order_col
                 , SUM(DECODE(val_col, 0, 1, 0))
                     OVER( ORDER BY order_col) the_group
                 , val_col
          FROM     mhe_foo
          ORDER BY order_col)
ORDER BY order_col
/
DROP TABLE mhe_foo
/
Read up on analytics and fiddle with the demo script to understand it. But again: we could really use sample data...

MHE
Re: Running Total [message #299462 is a reply to message #299461] Tue, 12 February 2008 02:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think the case may be more complex.
I think that data may be positive and negative and when running sum become negative then it is ceilled to 0.
For instance:
value running sum
1     1
2     3
-4    -1 changed to 0
2     2 (and not 1)
...

But maybe I'm influenced by a same question posted several years ago on usenet.

Regards
Michel
Re: Running Total [message #299464 is a reply to message #299462] Tue, 12 February 2008 02:31 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
And this is exactly the reason why we need a test set.

MHE
Previous Topic: FORALL statement not recognized in sqlplus
Next Topic: Julian date Convesion
Goto Forum:
  


Current Time: Sat Dec 10 18:16:07 CST 2016

Total time taken to generate the page: 0.11417 seconds