Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

question on db2 sum function

 
rick collette
Ranch Hand
Posts: 208
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 823
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1923
Linux Postgres Database Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
... 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.)
 
rick collette
Ranch Hand
Posts: 208
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks, guys:

Sorry, I forgot to put group by clause. DISTINCT is not needed in this case.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic