CODEWITHSUNDEEP
mysql
Chris
Chris

Reputation: 570

Grouped Query (Pivot style?)

I thought this would be simple, but I'm struggling (might be Friday brains)

I have a table:

Staff      department     job_date       job             Hours
---------------------------------------------------------------
Chris      Software       2011-03-02     Writing Code    3
Chris      Software       2011-03-04     Holiday         7
Bob        QA             2011-03-02     Testing         4
Frank      Design         2011-03-11     Meeting         1

Now, I need a query that will "group" the results by day, based on whats in the job field, ie.. So I get a sum of hours spent per employee doing work and also for holidays:

Resultset:

Staff          Worked Hours       Holiday Hours  Department
---------------------------------------------------------------
Chris           3                  7              Software
Bob             4                  0              QA
Frank           1                  0              Design
---------------------------------------------------------------
Total           8                  7

Thanks for any help you can give!

Chris

Upvotes: 2

Views: 101

Answers (2)

RichardTheKiwi
RichardTheKiwi

Reputation: 107776

select staff,
       sum(case when job='holiday' then 0 else hours end) `Worked Hours`,
       sum(case when job='holiday' then hours else 0 end) `Holiday Hours`,
       department
from tbl
group by staff, department

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425683

SELECT  department, staff,
        SUM(IF(job = 'Holiday', 0, hours)) AS work_hours,
        SUM(IF(job = 'Holiday', hours, 0)) AS holiday_hours
FROM    mytable
GROUP BY
        department, staff WITH ROLLUP

Upvotes: 1

Related Questions