sql - Join same table twice for count in different columns -

i have 2 tables

a +----+-------+ | id | user  | +----+-------+ |  1 | user1 | |  2 | user2 | |  3 | user3 | +----+-------+  b +----+--------+------+ | id | userid | type | +----+--------+------+ |  1 |      1 |    | |  2 |      1 | b    | |  3 |      1 | c    | |  4 |      2 |    | |  5 |      2 | b    | |  6 |      2 | c    | |  7 |      3 |    | |  8 |      3 | c    | +----+--------+------+  userid fk table a.id 

i'm trying count of each type , type permutations below single sql query. (e.g count a^b means number of users has type , b)

+---------+---------+---------+-----------+-----------+-----------+-------------+ | count | count b | count c | count a^b | count a^c | count b^c | count a^b^c | +---------+---------+---------+-----------+-----------+-----------+-------------+ |       3 |       2 |       3 |         2 |         3 |         2 |           2 | +---------+---------+---------+-----------+-----------+-----------+-------------+ 

or separate query each permutation count.

i tried below query count type , b separately , didn't work.

select count(b1.type) count_a, count(b2.type) count_b  join b on a.id = b.user_id b1.type = 'a' or b2.type = 'b'  group a.id;  +---------+---------+ | count | count b | +---------+---------+ |       3 |       2 | +---------+---------+ 

you can write:

select count(case when "types" @> array['a'] 1 end) "count a",        count(case when "types" @> array['b'] 1 end) "count b",        count(case when "types" @> array['c'] 1 end) "count c",        count(case when "types" @> array['a','b'] 1 end) "count a^b",        count(case when "types" @> array['a','c'] 1 end) "count a^c",        count(case when "types" @> array['b','c'] 1 end) "count b^c",        count(case when "types" @> array['a','b','c'] 1 end) "count a^b^c"   ( select array_agg("type"::text) "types"            "b"           group "userid"        ) t ; 

the idea first use subquery produces, each user, array containing his/her types; outer query counts arrays contain each set of types.

you can see in action @ http://sqlfiddle.com/#!15/cbb45/1. (i've included there modified version of subquery, see how works.)

some relevant postresql documentation:

• http://www.postgresql.org/docs/9.1/static/functions-aggregate.html (explains array_agg)
• http://www.postgresql.org/docs/9.1/static/functions-array.html (explains @>)