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