how to display absent or present result
please help me to fix this issue thanks
now showing like this result
________________________________________
Name TeacherNo Attendance
________________________________________
XYZ 993 5 Days Present
Abc 991 7 Days Present
123 955 2 Days Present
________________________________________
and i want like this
________________________________________________________________
Name TeacherNo Present Day Absent Days
_________________________________________________________________
XYZ 993 5 Days Present 3 Days Present
Abc 991 7 Days Present 0 Days Present
123 955 2 Days Present 4 Days Present
________________________________________________________________
This is code
$result1 = mysql_query("select name ,id ,`teacherno` as teacherno, CONCAT(count(`teacherno`),' Present days') as Attendance
from tattendance
where `Attendance` = 'present' AND date BETWEEN '2013-04-01' AND '2013-04-07' group by `teacherno` order by attendance desc");
Are you looking for something like the following query:
SELECT name, teacherno,
CONCAT(COALESCE(SUM(CASE WHEN Attendance = 'present' THEN 1 END),0),' Present days') as Present,
CONCAT(COALESCE(SUM(CASE WHEN Attendance = 'absent' THEN 1 END),0),' Absent days') as Absent
FROM tattendance
GROUP BY teacherno
This usese SUM with CASE to add up the days depending on the Attendance column. I also included COALESCE to return 0 instead of NULL.