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,

Cast your sum to numeric before executing the calculation

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:

thanks,

hour function makes this thing work.

regards,