MYSQL inner join counting only once depending on two conditions

I have a SQL query where I’m joining three tables that have information about employees, their departments, their schedules, if they are in vacations or not and reasons why they skipped work on an specific date. In one of the tables I have as foreign keys the employeeId, the scheduleId and the date in which the employee had the specific schedule. Schedule can be a time range or a reason why the employee was not at the workplace that day (including vacations).

The code is as follows:

SELECT DATE_FORMAT(date,'%Y-%m') AS date_frame, department, COUNT(DISTINCT employeeId) AS total_emp
FROM records 
INNER JOIN employees ON employees.id=records.employeeId 
INNER JOIN schedulesReasons ON schedulesReasons.id=records.scheduleId
GROUP BY department, date_frame ORDER BY date_frame, department

What I’m trying to achieve is to get the total amount of employees that where on vacations that month to compare it with the total amount of employees per department, but I can’t get it.

This is what I’m trying by the moment:

SUM(scheduleReason = 'Vacations') as emp_in_vacations, COUNT(DISTINCT employeeId) AS total_emp

But it is bringing me the sum of all the days that are categorize as vacations. I want it to be filtered by employeeId so I will just get the amount of employees on vacations and not the sum of the days.

Here is an example of the data I have:

Records
| id |     date   | employeeId | scheduleId |
| 1  | 2021-01-01 |     1      |     1      |
| 2  | 2021-01-02 |     1      |     1      |

schedulesReasons
| id |     scheduleReason   |
| 1  |       'Vacations'    |

Employees
| id |     name    |   department   |
| 1  |  'John Doe' |       IT       |

So I want the query to result in IT having one employee in vacations, but my query is bringing me the amount of days that he was in vacations.

Desired result:

date_frame | department |  emp_in_vacations | total_emp  |
   2021-01 |     IT     |          1        |     1      |

Current result:

date_frame | department |  emp_in_vacations | total_emp  |
   2021-01 |     IT     |          2        |     1      |

Answer

Instead of:

SUM(scheduleReason = 'Vacations')

which counts all the rows of schedulesReasons where the condition scheduleReason = 'Vacations' is true, you can do it with conditional aggregation:

COUNT(DISTINCT CASE WHEN schedulesReasons.scheduleReason = 'Vacations' THEN Records.employeeId END) AS emp_in_vacations

Advertisement

Answer