Cumulative Field

Divardo · #1 (**permalink**) 08-14-06, 16:45

I have four columns in a table. One called person, days, hours and cumulative. Every day the person works and as a result creates a new entry and thus an entire new row. From this, the day increases e.g. 1 - 2 - 3 etc in each row. The hours that they work is different each day. What i am hoping to achieve is have the forth column showing the total hours that person has work this the first day they started. Any ideas...

Tyveleyn · #2 (**permalink**) 08-14-06, 17:39

Hi,

Since you posted your question in the SQL forum I presume that you're looking for a query. Her I've got one:

Code:

SELECT person, days, hours,
        (SELECT sum(hours)
         FROM table b
         WHERE b.person = a.person
         AND b.days <= a.days) AS cumulative
FROM table a

This should work, at least it does on an Informix database. I'm not shure whether this is standard SQL or not...

If it's not a query you want but a way of dynamicly filling the fourth column at every insert you could use the subquery inside a trigger to calculate the value to be inserted in the last column.

Regards

Littlefoot · #3 (**permalink**) 08-14-06, 17:48

Which database do you use? On Oracle, you could create a database trigger which would populate the required columns; this would be a nice and clean; for example:

Code:

CREATE OR REPLACE TRIGGER trg_hours
BEFORE INSERT ON HOURS_CUM
FOR EACH ROW
BEGIN
  SELECT 
     NVL(MAX(t.days) + 1, 1),
     NVL(SUM(t.HOURS_worked), 0) + :NEW.HOURS_worked
  INTO :NEW.days, :NEW.cumulative
  FROM HOURS_CUM t
  WHERE person = :NEW.person;
END;

Direct table insert won't be possible (in Oracle) because table is mutating and records can't be accessed (for example, entering 2 hours for person number 1)

Code:

INSERT INTO HOURS_CUM 
(person, days, HOURS_worked, cumulative)
(SELECT 
   1,  
   NVL(MAX(t.days) + 1, 1), 
   2, 
   NVL(SUM(t.HOURS_worked), 0) + 2
 FROM HOURS_CUM t
 WHERE t.person = 1
);

I guess the same goes for another databases; if you can't use triggers, you'll need to figure out how to bypass such a limitation (if it exists).

Peter.Vanroose · #4 (**permalink**) 08-15-06, 04:26

Quote:

Originally Posted by Littlefoot

On Oracle, you could create a database trigger

The same holds for DB2.
But replace NVL by COALESCE then. (Also works on Oracle.)

Peter.Vanroose · #5 (**permalink**) 08-15-06, 04:49

Quote:

Originally Posted by Divardo

... have the forth column showing the total hours ...

An other solution consists of redefining your table:
- Have a differently named table with just the first three columns.
- Create a VIEW (with the name of the old table):

Code:

    CREATE VIEW mytable AS
        SELECT person, days, hours,
               (SELECT SUM(hours)
                FROM   oldtable
                WHERE  person = t.person AND days <= t.days) AS cumulative
        FROM   oldtable AS a

This way, you may insert into "oldtable", and read from "mytable".

Tyveleyn · #6 (**permalink**) 08-15-06, 05:33

Can anyone tell me how to size the "Code" section so it won't appear with a fixed size and a scrollbar? I've been trying some things now, without the wanted result...

Thanks,
Hans

BTW: With Informix it's possible to dynamically store a value in a column of an inserting row. It has to be done with an insert trigger that a invokes a stored function which returns it's value INTO the specified column.

Peter.Vanroose · #7 (**permalink**) 08-15-06, 05:48

Don't think this is possible...
It's a feature of the dBforums lay-out, I'm afraid.

LKBrwn_DBA · #8 (**permalink**) 08-15-06, 09:26

Quote:

Originally Posted by Littlefoot

Which database do you use? On Oracle, you could create a database trigger which would populate the required columns; this would be a nice and clean;

Unfortunatrely this trigger solution posted may produce a "mutating table" error.

Littlefoot · #9 (**permalink**) 08-15-06, 10:51

It may, but - on the other hand - it doesn't have to. Let me try:

Code:

SQL> create table hours_cum
  2  (person number,
  3   days   number,
  4   hours_worked number,
  5   cumulative   number);

Table created.

SQL> CREATE OR REPLACE TRIGGER trg_hours
  2  BEFORE INSERT ON HOURS_CUM
  3  FOR EACH ROW
  4  BEGIN
  5    SELECT
  6       NVL(MAX(t.days) + 1, 1),
  7       NVL(SUM(t.HOURS_worked), 0) + :NEW.HOURS_worked
  8    INTO :NEW.days, :NEW.cumulative
  9    FROM HOURS_CUM t
 10    WHERE person = :NEW.person;
 11  END;
 12  /

Trigger created.

SQL> insert into hours_cum (person, hours_worked) values (1, 8);

1 row created.

SQL> select * from hours_cum;

    PERSON       DAYS HOURS_WORKED CUMULATIVE
---------- ---------- ------------ ----------
         1          1            8          8

SQL> insert into hours_cum (person, hours_worked) values (1, 7);

1 row created.

SQL> select * from hours_cum;

    PERSON       DAYS HOURS_WORKED CUMULATIVE
---------- ---------- ------------ ----------
         1          1            8          8
         1          2            7         15

SQL> insert into hours_cum (person, hours_worked) values (2, 5);

1 row created.

SQL> select * from hours_cum;

    PERSON       DAYS HOURS_WORKED CUMULATIVE
---------- ---------- ------------ ----------
         1          1            8          8
         1          2            7         15
         2          1            5          5

SQL>

It seems quite OK to me ... did you have something else in mind, what I don't see at the moment?

Peter.Vanroose · #10 (**permalink**) 08-15-06, 12:46

In summary, there's not much difference between the "trigger" solution and the "view" solution -- i.e., you insert into the first three columns, and you may read the four columns.
Advantages of the "trigger" solution:
- only need one table in all SQL
- 4th column is calculated once, and stored; no recalculation on read
Advantages of the "view" solution:
- less danger of attempt to insert into 4th column, since the table to be inserted only has 3 columns, and an attempt to insert into the view will tell you it's a view
- a trigger is invisible for SQL; with the view it is clearer for the SQL user that the 4th column is a calculated one.

Tyveleyn · #11 (**permalink**) 08-15-06, 13:03

For what it's worth, I would prefer the view-method. Next to the advantages Peter summarized it complies with the first normalform to!

BTW: Did anyone notice the initial asker, Divardo, never replied to this thread?

Bye

Littlefoot · #12 (**permalink**) 08-15-06, 14:24

Never mind; we had a nice little chat here

Internet Services

Web Development

Digital Marketing

Consumer Tech