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?
Joined: Aug 02, 2004
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.
[ August 13, 2004: Message edited by: Julian Kennedy ] [ August 13, 2004: Message edited by: Julian Kennedy ]
... 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.