Aggregate function of column that has at least one type of value in a different column

I have table below as

ID | Blood_Type |  Size
B2     A           100
B2     B           200
C2     C           102
C2     O            88
G4     I            44
G4     A            100

How can I query my table above to get average of only my IDs that have at least have one row with blood type of A

Expected output:

ID | Avg_Size 
B2    150
G4     72

Thanks!

Answer

Tim’s answer is good, a simpler albeit perhaps not how you would want to do it, other way, is doing HAVING in long form

SELECT id,
    avg_size
FROM (
    SELECT id, 
        AVG(size) AS avg_size,
        SUM(IFF(blood_type = 'A', 1, 0)) AS a_count
    FROM table
    GROUP BY id
)
WHERE a_count > 1;

so you can ether use SUM or COUNT, they both ignore nulls, which is the implicit result of Tim’s CASE WHEN Blood_Type = 'A' THEN 1 END is the same as

CASE 
    WHEN Blood_Type = 'A' THEN 1 
    ELSE NULL
END

if you use SUM it can handle null’s or zeros thus the IFF can be used, which I like as it’s smaller and more explicit about what is happening.

thus Tim’s answer can be swapped to a SUM(IFF like:

SELECT 
    id,
    AVG(size) AS avg_size
FROM table
GROUP BY id
HAVING SUM(IFF(Blood_Type = 'A',1, 0) > 0;