Employee Attendance Record

The problem

Two tables describe a company's attendance. Employees holds one row per person; Attendance holds one row per logged status on a date and may contain duplicates.

For each employee, return employee_id, employee_name, and three counts — days_present, days_absent, days_late — ordered by employee_id. Every employee must appear in the result even if they have no attendance rows at all.

Core idea: pivot rows into columns with conditional aggregation

The status values live down the rows — one row per Present/Absent/Late event. The output needs them across the columns — one count each, side by side. That row-to-column reshape is the whole problem. The tool for it is conditional aggregation: wrap a CASE inside an aggregate so each status is counted into its own column.

SUM(CASE WHEN a.status = 'Present' THEN 1 ELSE 0 END) AS days_present

For one employee's group of rows, the CASE emits 1 on the matching rows and 0 everywhere else; SUM then totals just the matches. Run three of these in parallel and you have all three counts in a single pass over the group. COUNT(CASE WHEN ... THEN 1 END) works identically — COUNT ignores NULL, so you drop the ELSE 0 and let non-matches fall through to NULL.

Why LEFT JOIN, not INNER JOIN

The requirement "every employee must appear" is the load-bearing constraint. An INNER JOIN silently drops any employee with zero attendance rows — a new hire, someone on leave — because there is no matching row to join to. A LEFT JOIN from Employees keeps every employee and fills the missing Attendance columns with NULL.

Those NULLs are then harmless to the counts: CASE WHEN a.status = 'Present' is neither true for a NULL status, so it lands in the ELSE 0 branch, and the employee correctly scores 0, 0, 0 instead of vanishing.

The full solution

SELECT e.employee_id,
       e.employee_name,
       SUM(CASE WHEN a.status = 'Present' THEN 1 ELSE 0 END) AS days_present,
       SUM(CASE WHEN a.status = 'Absent'  THEN 1 ELSE 0 END) AS days_absent,
       SUM(CASE WHEN a.status = 'Late'    THEN 1 ELSE 0 END) AS days_late
FROM Employees e
LEFT JOIN Attendance a ON e.employee_id = a.employee_id
GROUP BY e.employee_id, e.employee_name
ORDER BY e.employee_id;

Reading it as a pipeline: LEFT JOIN attaches each employee's attendance rows (or a single all-NULL row if they have none); GROUP BY collapses each employee into one group; the three SUM(CASE ...) expressions count statuses within that group; ORDER BY sorts the final rows. Against the sample data this yields Alice 1, 1, 0, Bob 1, 0, 1, Charlie 2, 0, 0.

Pitfalls

• INNER JOIN drops employees with no rows. The single most common wrong answer. Use LEFT JOIN from Employees; verify by checking that an employee with zero attendance rows still appears with 0, 0, 0.
• Putting the filter in WHERE instead of CASE. A WHERE a.status = 'Present' clause filters out every non-Present row before grouping, so you cannot count Absent and Late in the same query — and on a LEFT JOIN it also discards the NULL rows of employees who never attended, re-introducing the INNER-JOIN bug. Keep the conditioning inside the aggregate.
• GROUP BY portability. List both grouping columns — GROUP BY e.employee_id, e.employee_name — for portability across engines. Strict SQL engines (and MySQL with ONLY_FULL_GROUP_BY enabled but without functional-dependency detection) reject a SELECT of e.employee_name when it is neither grouped nor aggregated. Note this is not the case for PostgreSQL on this schema: PostgreSQL implements the SQL standard's functional-dependency relaxation, so because employee_id is the declared primary key of Employees, GROUP BY e.employee_id alone is accepted and you may freely select e.employee_name. The advice to group by both columns is about cross-engine portability, not about appeasing PostgreSQL.
• COUNT(*) vs the CASE counts. COUNT(*) over the group counts joined rows, which includes the phantom NULL row from a LEFT JOIN with no matches — so it would report 1 for an employee with no attendance. The SUM(CASE ...) columns sidestep this by counting only matching statuses.

Generalize the pattern

This is the canonical conditional aggregation / row-to-column pivot shape, and it recurs constantly: orders by status (paid / pending / refunded), survey answers by choice, log events by level. The recipe is always the same three moves — (1) LEFT JOIN from the entity table you must fully preserve, (2) GROUP BY the entity, (3) one SUM(CASE WHEN category = X THEN 1 ELSE 0 END) per output column. Once you see "one count per category, side by side, every entity present," reach for this template.

Source

Adapted and expanded from the Knowledge Guide lesson Employee Attendance Record (Databases › SQL Practice Problems), file site/databases/sql-practice-problems/022-employee-attendance-record.html, with corrected coverage of PostgreSQL's primary-key functional-dependency handling in GROUP BY.