Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    16

    Unanswered: question on sum function

    hello, guys:


    I have a question about sum function using DB2. I am using DB2 Universal Database Version 7.2 (persona; edition) on Win 2000.

    If I have 2 tables:

    create table person{
    person_id char(10) not null,
    .....
    primary key(person_id)
    };

    create table job{
    job_id char(20) not null,
    start_date TIMESTAMP;
    stop_date TIMESTAMP;
    person_id char(10),
    primary key(job_id)
    };

    You can see from the above 2 tables, job table has a foreign key pointing
    to person table. A person may have multiple jobs.

    What I try to do is to calculate a person's total working hours for all of his jobs:

    SELECT DISTINCT p.id, sum(j.stop_date - j.start_date) from person as p, job as j where p.person_id = j.person_id

    From the above sql statement, it seems the result of summation is a BigDecimal, after I turn it into long and divide it by 60 * 60 * 1000 (an hour has that much milliseconds, right?), I got wrong hours.

    Could anyone point out my mistakes?

    regards,

  2. #2
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    Cast your sum to numeric before executing the calculation
    F. Celaia
    DBA Sybase/DB2/Oracle/MS-SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by rick_cold
    SELECT DISTINCT p.id, sum(j.stop_date - j.start_date) from person as p, job as j where p.person_id = j.person_id
    I don't think your approach with using DISTINCT is correct. What you really need is to group results by person and then add up hours within each group, like this:
    Code:
    select p.person_id, sum(hour(j.stop_date - j.start_date)) from person p, job j where p.person_id = j.person_id group by p.person_id

  4. #4
    Join Date
    Jul 2003
    Posts
    16
    thanks,

    hour function makes this thing work.

    regards,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •