File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes  question on db2 sum function  Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark " question on db2 sum function  " Watch " question on db2 sum function  " New topic

question on db2 sum function

rick collette
Ranch Hand

Joined: Mar 22, 2002
Posts: 208
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, 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?

Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
Hi Rick,

Firstly you don't need DISTINCT as the PK on person ensures that person_id is unique and the join ensures the result set is correct.

Secondly, and most importantly, you need to add GROUP BY (or p.person_id? Which is it?) to your query.

SUM() is an aggregate function. You want the sum of all hours worked by each distinct person. Without the GROUP BY what you're getting is the sum of all hours worked by everyone.

Some RDBMS (e.g. Oracle) insist that you use GROUP BY if you have any non-aggregate expressions in the SELECT clause. This helps avoid errors like this but prevents legitimate uses of this kind of query.

Hope this helps.


[ August 13, 2004: Message edited by: Julian Kennedy ]
[ August 13, 2004: Message edited by: Julian Kennedy ]
Stefan Wagner
Ranch Hand

Joined: Jun 02, 2003
Posts: 1923

... and as long as you don't use the person-table, you needn't join it:
sum(stop_date - start_date) AS duration
FROM job
GROUP BYperson_id"
and if the result is in milliseconds, you don't have a BigDecimal, but a BigInteger - or perhaps only an ordinary long.

(Not sure.)
rick collette
Ranch Hand

Joined: Mar 22, 2002
Posts: 208
Thanks, guys:

Sorry, I forgot to put group by clause. DISTINCT is not needed in this case.
I agree. Here's the link:
subject: question on db2 sum function
It's not a secret anymore!