Select only rows where all values in a column match a value for an id

Given the table

seq_id
seq_status

1
A

1
B

2
A

2
A

3
A

4
B

5
C

I want to select the distinct seq_id's where the seq_status is A
(2 and 3) and not return if one of the values is different

so far I have something that looks like this

SELECT distinct(s.seq_id) FROM sequence s
having s.seq_id IN (
    select z.seq_id
    from sequence z
    group by z.seq_id, z.seq_status
    having z.seq_status = "A" )

But that returns any seq_id with seq_status A and I'm looking for only seq_id where the seq_status all match the required value

Yet another alternative:

# All non-NULL seq_status values per seq_id contain 'A'
SELECT s.seq_id
FROM sequence AS s
GROUP BY s.seq_id
HAVING SUM(s.seq_status = 'A') = COUNT(*);

That works because comparison operations result in a value of 1 (TRUE), 0 (FALSE), or NULL.

There are a large number of alternatives, including:

HAVING COUNT(DISTINCT seq.status) = 1 AND MIN(seq_status)='A'
HAVING SUM(s.seq_status <> 'A' OR seq.status IS NULL) = 0

Not to mention queries written using EXISTS or = ALL.

Or if you want to disregard NULLs:

SELECT s.seq_id
FROM sequence AS s
GROUP BY s.seq_id
HAVING MIN(s.seq_status) = 'A'
   AND MAX(s.seq_status) = 'A';

db<>fiddle demo

StackExchange Database Administrators Q#313625, answer score: 6