can i can use two group by in sql query-CodePudding

I have these two tables:

Covids

    | CovidId | StaffId | CovidStartDate | CovidFinishDate | 
      -------   -------   --------------   ---------------
        1           1       21.01.2021         15.01.2021
        2           1       26.04.2021         16.04.2021
        3           2       21.02.2021         14.01.2021
        4           3       22.03.2021         17.03.2021
        5           3       15.04.2021         30.04.2021

WorkingHours

    | WorkId | StaffId | WorkHours| 
     -------   -------   ---------
        1          1          8
        2          2          9
        3          3          8

I want to show how many times they had covid by working hour expected result

    | WorkHour | CovidCount| 
     -------      -------   
        8           4          
        9           1

I wrote these SQL statements, but I was unable to bring them together:

SELECT StaffId, COUNT(*) AS covidCount 
FROM Covids 
GROUP BY StaffId

SELECT WorkHours 
FROM WorkingHours 
GROUP BY WorkHours

CodePudding user response：

Of course two separate queries won't "unite". You need to join the tables and then do the aggregation.

SELECT w.workhours AS workhour,
       count(*) AS covidcount
       FROM workinghours AS w
            LEFT JOIN covids AS c
                      ON c.staffid = w.staffid
       GROUP BY w.workhours;