Grouping columns in MYSQL, complex query

Question

I have this MYSQL Query and I need the columns to be grouped up next to eachother, I just can't seem to do it....

SELECT Supervisor,
   (CASE WHEN DAYOFWEEK(workdate) = 2 THEN (COUNT(`ID Number`)) END) AS `Monday`,
   (CASE WHEN DAYOFWEEK(workdate) = 3 THEN (COUNT(`ID Number`)) END) AS `Tuesday`,
   (CASE WHEN DAYOFWEEK(workdate) = 4 THEN (COUNT(`ID Number`)) END) AS `Wednesday`,
   (CASE WHEN DAYOFWEEK(workdate) = 5 THEN (COUNT(`ID Number`)) END) AS `Thursday`,
   (CASE WHEN DAYOFWEEK(workdate) = 6 THEN (COUNT(`ID Number`)) END) AS `Friday`,
   (CASE WHEN DAYOFWEEK(workdate) = 7 THEN (COUNT(`ID Number`)) END) AS `Saturday`
FROM payroll.employeedatanew_copy
INNER JOIN payroll.employeehours ON employeedatanew_copy.`ID Number` = employeehours.employeeid
WHERE workdate BETWEEN "2013-10-28" AND "2013-11-02"
GROUP BY workdate, supervisor

Here is my result: https://i.sstatic.net/l9kSz.png

This is what I want: https://i.sstatic.net/bRNmJ.png

In the "what I want" image, the value for each row is that same for each workday, is that what is expected? — Mike Brant
– Mike Brant, Commented Dec 3, 2013 at 0:22
@MikeBrant, yeah I had just used the same dataset for each date, so values are the same :). — atomapps
– atomapps, Commented Dec 3, 2013 at 0:51
Thanks Barranka, but that just wasn't enough, perfect answer is below! — atomapps
– atomapps, Commented Dec 3, 2013 at 0:51

Mosty Mostacho · Accepted Answer · 2013-12-03 00:22:25Z

4

I guess you're looking for this:

SELECT Supervisor,
   COUNT(CASE WHEN DAYOFWEEK(workdate) = 2 THEN 1 END) `Monday`,
   COUNT(CASE WHEN DAYOFWEEK(workdate) = 3 THEN 1 END) `Tuesday`,
   COUNT(CASE WHEN DAYOFWEEK(workdate) = 4 THEN 1 END) `Wednesday`,
   COUNT(CASE WHEN DAYOFWEEK(workdate) = 5 THEN 1 END) `Thursday`,
   COUNT(CASE WHEN DAYOFWEEK(workdate) = 6 THEN 1 END) `Friday`,
   COUNT(CASE WHEN DAYOFWEEK(workdate) = 7 THEN 1 END) `Saturday`
FROM payroll.employeedatanew_copy
JOIN payroll.employeehours ON employeedatanew_copy.`ID Number` = employeehours.employeeid
WHERE workdate BETWEEN "2013-10-28" AND "2013-11-02"
GROUP BY supervisor

Alternatively, the counts could be replaced with:

SUM(DAYOFWEEK(workdate) = 2) `Monday`

This works based on the fact that true equals 1 in MySQL but will not necessarily work in other DBMS.

answered Dec 3, 2013 at 0:22

Mosty Mostacho

43.6k16 gold badges99 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mike Brant Over a year ago

It should be noted that grouping by workdate basically put all workdate=2 rows first, then workdate=3 rows, etc. You don't want that grouping at all.

Mosty Mostacho Over a year ago

Right. That's why Darrent Bent was there more than once in different rows.

Collectives™ on Stack Overflow

Grouping columns in MYSQL, complex query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related