Basic SQL aggregate query

Question

I have an employee table, employees may have multiple peripherals. I want to bring back all employees that have a peripheral of type 1 or 2, but that don't have any others, and vice versa (i.e. have a peripheral of type other than 1 or 2 but not have any peripherals of 1 or 2). For each of these employees, there should be a flag to show which of the two categories/product types they have.

This query works, but feels a bit verbose:

SELECT employee_id,
       has_screen,
       has_keyboard
FROM
(
    SELECT e.employee_id,
    CASE WHEN SUM(CASE WHEN ep.type IN (1,2) THEN 1 ELSE 0 END) > 0 THEN 1 ELSE 0 END has_screen,
    CASE WHEN SUM(CASE WHEN ep.type > 2 THEN 1 ELSE 0 END) > 0 THEN 1 ELSE 0 END has_keyboard
    FROM employee e
    JOIN employee_peripheral ep
    ON e.employee_id = ep.employee_id
    GROUP BY e.employee_id
) T
WHERE has_screen <> has_keyboard

Answer 1

BOOL values

Your outer case statements convert a value to itself. SUM(CASE WHEN type IN (1,2) THEN 1 ELSE 0 END) > 0 is TRUE or FALSE, which are equivalent to 1 and 0.

WHERE vs HAVING

WHERE will filter rows from the input, which means that you can't use an aggregate. HAVING filters after the aggregation, which means you can drop the outer query.

Unnecessary JOIN

You are only using the employee_id from employee, which is present in employee_peripheral

Resulting query

SELECT employee_id,
SUM(CASE WHEN type IN (1,2) THEN 1 ELSE 0 END) > 0 AS has_screen,
SUM(CASE WHEN type > 2 THEN 1 ELSE 0 END) > 0 AS has_keyboard
FROM employee_peripheral
GROUP BY employee_id
HAVING (SUM(CASE WHEN type IN (1,2) THEN 1 ELSE 0 END) > 0) <> (SUM(CASE WHEN type > 2 THEN 1 ELSE 0 END) > 0)