Where column “c” is not null for each group in column “a” – give group “a” a value of 1

Column “C” is a flag indicator. I want to run a query where I can sum column “c” where it is not null. I would like 1 value for each group of column A.

For instance group “Test”, would have a total of 1. Group chance has a total of 1. Group “Play” has a total of 0.

I would like a total number as a result. Please view my code below.

A	C
Test	apple
Test	orange
Test
Test	apple
Chance
Chance	apple
Chance
Play
Play

SELECT
sum(case when distinct A and C is not null then 1 else 0 end) as total
FROM table

Answer

If you want a flag per a if any c has a value, then you can use:

select a, (case when count(c) > 0 then 1 else 0 end) as has_c
from t
group by a;

You can actually simplify this using the sign() function:

select a, sign(count(c)) as has_c
from t
group by a;

If you want this per row, then use window functions:

select t.*,
       (case when count(c) over (partition by a) > 0 then 1 else 0 end)
from t;

Obviously, if you just want to count the non-NULL values, you would use just count(c) without the case expression.