mercredi 2 mars 2016

Fastest way to get total count of employees , total count of employees who are absent in each department?

Department

+-------+------------+
| EmpId | DepId|
+-------+------------+
|     1 | IT         |
|     2 | admin      |
|     3 | IT         |
|     4 | IT         |
|     5 | admin      |
+-------+------------+

Attendance

+-------+--------+
| EmpId | Status |
+-------+--------+
|     1 | P      |
|     2 | P      |
|     3 | P      |
|     4 | A      |
|     5 | P      |
+-------+--------+

Desired Output :

+-------+------------+--------------+
| DepId | TotalCount | PresentCount |
+-------+------------+--------------+
| Admin |          2 |            2 |
| IT    |          3 |            2 |
+-------+------------+--------------+

My Query:

 SELECT DepId,
       COUNT(att.empid) total,
       sum(CASE WHEN status = 'P' THEN 1 ELSE 0 END) presetCount
FROM attendance att
INNER JOIN departments dep ON att.empid=dep.empid
GROUP BY DepId

Can this be optimized further? if yes how?

Aucun commentaire:

Enregistrer un commentaire