count True False, every day for a month and join from 2 table SQL

user_15986083 0
Viewed 53

I have an attendance table included wfo/wfh records and also have employees table too. I want to count is the employee working from home or office on every date, for every month. I need to join it to employees table too, to count those employees from all cities except 2. Can you guys please help constructed it? Thank you..

SELECT b.created_at,
       COUNT(CASE WHEN b.is_wfh = 'true' THEN 1 ELSE NULL END) AS 'WFH',
       COUNT(CASE WHEN b.is_wfh = 'false' THEN 1 ELSE NULL END) AS 'WFO'
FROM attendancetable b
JOIN employeetable a ON a.id = b.id
WHERE b.created_at BETWEEN '2022-01-01' AND '2022-01-31'
AND a.office_location NOT LIKE '%D%' AND a.office_location NOT LIKE '%E%'

sorry I'm not that good with english. here's the example

employee table

name     employee_id   office_location
======================================
Happy         1             A 
Sad           2             B
Angry         3             C
Hungry        4             D
Grumpy        5             E

attendance table

employee_id   created_at      is_wfh
======================================
     1        2022-01-01       true
     2        2022-01-01       true
     3        2022-01-01       false
     4        2022-01-01       false
     5        2022-01-01       false
     1        2022-01-02       false
     2        2022-01-02       true
     3        2022-01-02       false
     4        2022-01-02       true
    ...           ...          ...

expected (count wfh and wfo) result with conditions: employee from A, B, C only AND date between 2022-01-01 and 2022-01-31

created_at      wfh     wfo
============================
2022-01-01       2       1
2022-01-02       1       2

I hope my explanation is enough.. thankyou! ...

Sorry I wanna ask more question. If there's time record at created_at field, what should I do to combine all those counting of wfh/wfo?

count join mysql
1 Answers

When doing aggregate function always you need a group by clause. I replaced not like condition with e.office_location IN ('A','B','C') because you only want employee from A, B, C only .

Try:

SELECT a.created_at,
       COUNT(CASE WHEN is_wfh = 'true' THEN 1 ELSE NULL END) AS 'WFH',
       COUNT(CASE WHEN is_wfh = 'false' THEN 1 ELSE NULL END) AS 'WFO'
FROM attendance a
JOIN employee e ON a.employee_id = e.employee_id
WHERE a.created_at BETWEEN '2022-01-01' AND '2022-01-31'
AND e.office_location IN ('A','B','C')
group by a.created_at
order by a.created_at asc;

Result:

created_at    WFH WFO
2022-01-01     2   1
2022-01-02     1   2

Demo

user_164619520
Related