KyLim KyLim - 1 month ago 7
SQL Question

SQL PIVOT group by 2 columns

I have a attendance table as below i want group them by time and section,status is null mean that the employee is absent :

enter image description here

Any idea how to generate output like below?

enter image description here

my current code :

SELECT TIME,COUNT(SECTION) AS SECTION,COUNT(STATUS) AS COUNT
FROM attendance_record
GROUP BY TIME,SECTION
ORDER BY TIME

Answer

If I understand your question, just use conditional aggregation:

SELECT TIME, SECTION, COUNT(*) as TOTAL,
       COUNT(STATUS) AS IN, ( COUNT(*) - COUNT(STATUS) ) as ABSENT
FROM attendance_record
GROUP BY TIME, SECTION
ORDER BY TIME
Comments