It's not a secret anymore!*
The moose likes JDBC 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


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark " question on db2 sum function  " Watch " question on db2 sum function  " New topic
Author

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 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,
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 p.id (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.

Jules

[ 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:
"SELECT DISTINCT person_id,
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.)


http://home.arcor.de/hirnstrom/bewerbung
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.
 
 
subject: question on db2 sum function
 
Similar Threads
Restore problem
DAOs and complex/aggregated objects
JDBC Help figuring it out
@JoinTable or @JoinCol...
how to create sequence in mysql