Getting all the values in one query that aren't in another with a group by

Question

Given that I am using Redshift, how would I get the counts for a query that asks:

Given table A and table B, give me all the count of values in Table A for that grouping that aren't in table B;

So if table A and B look like:

Table A

Id | Value
==========
 1 | "A"
 1 | "B"
 2 | "C"

And table B:

Id | Value
==========
 1 | "A"
 1 | "D"
 2 | "C"

I would want:

Id | Count
==========
 1 |  1
 2 |  0

Answer 1

Use except and subquery

with a as
(
select 1 as id, 'A' as v
union all
select 1,'B'
union all
select 2,'C'
),b as
(
select 1 as id, 'A' as v
union all
select 1,'D'
union all
select 2,'C'
), c as
(
select id,v from a except select id,v from b
)
select id,sum ( (select count(*) from c where c.id=a.id and c.v=a.v))
from a group by id

output

id  cnt
1   1
2   0

online demo which will work in redshift

Answer 2

You can use left join and group by:

select a.id, sum( (b.id is null)::int )
from a left join
     b
     on a.id = b.id and a.value = b.value
group by a.id;