Table:Attendance(StudentID, SubjectID, Date, Status)where Status is 'P' or…
2026
Table:
Attendance(StudentID, SubjectID, Date, Status)where Status is 'P' or 'A'.
Write a single query — no window functions — to display:
StudentIDtotal classes
total present
total absent
attendance percentage rounded to 2 decimal places
remark:
'Detained'if< 75%, else'Eligible'
(i) For a student with 47P out of 63 classes, what is the remark? Show calculation.
(ii) A student uses COUNT(Status='P') to count present days. Explain precisely why this is wrong and what it actually counts.
Show answer & explanation
A single query — no window functions
SELECT
StudentID,
COUNT(Status) AS "Total Classes",
SUM(CASE WHEN Status = 'P' THEN 1 ELSE 0 END) AS "Total Present",
SUM(CASE WHEN Status = 'A' THEN 1 ELSE 0 END) AS "Total Absent",
ROUND((SUM(CASE WHEN Status = 'P' THEN 1 ELSE 0 END) * 100.0) / COUNT(Status), 2) AS "Attendance Percentage",
CASE
WHEN (SUM(CASE WHEN Status = 'P' THEN 1 ELSE 0 END) * 100.0) / COUNT(Status) < 75.0
THEN 'Detained'
ELSE 'Eligible'
END AS "Remark"
FROM Attendance
GROUP BY StudentID;
(i) Calculation and Remark for 47P out of 63 classes
Given:
Total Present = 47
Total Classes = 63
Calculation Steps:
Divide total present days by total classes: 47 / 63 ≈ 0.74603
Multiply by 100 to get the percentage: 0.74603 * 100 = 74.603%
Round to 2 decimal places: 74.60%
(ii) Why count(Status='P') is wrong?
COUNT(Status='P')is incorrect for counting present students because:
COUNT()counts non-NULL values.The expression
Status='P'returns TRUE or FALSE for every row.Both TRUE and FALSE are non-NULL values.
Therefore, it counts almost all rows instead of only present rows.
Correct Method:
SUM(CASE WHEN Status='P' THEN 1 ELSE 0 END)This counts only rows where Status is 'P'.
A video solution is available for this question — log in and enroll to watch it.