skip to Main Content

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

2

Answers


  1. 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. 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