SQL SELECT statement help

[Anon]

I have a table, jobhours, which has as INT columns "job_id" and "hours". "job_id" can be any value, but if it is = to 9000, 9001 or 9002 (holidays, sick day & vacation, respectively), they need to be treated specially.

Right now, I run two queries on the database, one to find the sum of regular hours each employee works, and one to find the sum of the special hours. I'm using the following queries:

For selecting regular jobs...

SELECT employees.emp_id, SUM(hours)
FROM jobhours, employees
WHERE jobhours.emp_id=employees.emp_id
AND jobhours.job_id != 9000
AND jobhours.job_id != 9001
AND jobhours.job_id != 9002
GROUP BY employees.emp_id
ORDER BY employees.emp_id;

For selecting special jobs:

SELECT employees.emp_id, SUM(hours)
FROM jobhours, employees
WHERE jobhours.emp_id=employees.emp_id
AND (jobhours.job_id = 9000
OR jobhours.job_id = 9001
OR jobhours.job_id = 9002)
GROUP BY employees.emp_id
ORDER BY employees.emp_id;

How can I combine these two queries into one statement? I'd like the query to return something like this:

emp_id | SUM(reg_hours) | SUM(9000_hours | SUM(9001_hours | etc...

Thanks!

[Anon]

SELECT employees.emp_id,
SUM(case when jobhours.job_id not in (9000,9001,9002) then
hours else 0 end) as hours_regular,
SUM(case when jobhours.job_id = 9000 then
hours else 0 end) as hours_9000,
SUM(case when jobhours.job_id = 9001 then
hours else 0 end) as hours_9001,
SUM(case when jobhours.job_id = 9002 then
hours else 0 end) as hours_9002
FROM jobhours, employees
WHERE jobhours.emp_id=employees.emp_id
GROUP BY employees.emp_id
ORDER BY employees.emp_id;

[djurnoce]

Perfect! Thank you very much. I was about to try it with creating a tempory table (with a column for each of the hours groups that I wanted), populating it with the data I needed, then querying that table. It seemed a bit overboard for such a simple problem, your solution is much more elegant. Thanks again!