Question 113406 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Choose the person who worked the most night shifts

How do you choose the employee who has worked the most night shifts of all time? There are 2 tables, one with workers, the second with night shifts, in which 2 people work per shift.

Users:

id name
1 Oliver
2 Harry
3 Jacob

Hours:

id NightShift1 NightShift2
1 1 3
2 2 2
3 3 1
4 3 2
5 2 2
6 1 2
7 1 3
8 3 1
Tags:

2

Answers


  1. 0

    You can UNION the Hours table on top of itself and then group by the user id to see who has the highest count:

    SELECT COUNT(*) as nightshiftcount,  userid 
FROM (
    SELECT NightShift1 as userid FROM Hours 
    UNION ALL SELECT NightShift2 FROM Hours
    ) as hrs 
ORDER BY nightshiftcount DESC 
LIMIT 1

    If you need the name, you can just INNER JOIN to that table in that outer FROM clause and pull that column through.

    Login or Signup to reply.
  2. 0

    To do this you can to essentially loop over the hours table twice; you do this by joining an ad hoc table specifying which shift you are looking at:

    select users.id, users.name
from hours
join (select 1 position union all select 2) position
join users on users.id=if(position=1,hours.NightShift1,hours.NightShift2)
group by users.id
order by count(*) desc
limit 1
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search