I have 2 tables
A
+----+-------+
| Id | User |
+----+-------+
| 1 | user1 |
| 2 | user2 |
| 3 | user3 |
+----+-------+
B
+----+--------+------+
| Id | UserId | Type |
+----+--------+------+
| 1 | 1 | A |
| 2 | 1 | B |
| 3 | 1 | C |
| 4 | 2 | A |
| 5 | 2 | B |
| 6 | 2 | C |
| 7 | 3 | A |
| 8 | 3 | C |
+----+--------+------+
UserId is FK from table A.Id
I'm trying to get count of each type and type permutations as below with single SQL query. (e.g count A^B means that number of users who has type A and B)
+---------+---------+---------+-----------+-----------+-----------+-------------+
| Count A | 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 for each permutation count.
I tried below query to get count for type A and B separately and it didn't work.
SELECT count(b1.type) AS count_a, count(b2.type) AS count_b FROM A
JOIN B on A.id = B.user_id
WHERE b1.type = 'A' or b2.type = 'B'
GROUP BY A.id;
+---------+---------+
| Count A | Count B |
+---------+---------+
| 3 | 2 |
+---------+---------+
You can write:
select count(case when "Types" @> array['A'] then 1 end) as "COUNT A",
count(case when "Types" @> array['B'] then 1 end) as "COUNT B",
count(case when "Types" @> array['C'] then 1 end) as "COUNT C",
count(case when "Types" @> array['A','B'] then 1 end) as "COUNT A^B",
count(case when "Types" @> array['A','C'] then 1 end) as "COUNT A^C",
count(case when "Types" @> array['B','C'] then 1 end) as "COUNT B^C",
count(case when "Types" @> array['A','B','C'] then 1 end) as "COUNT A^B^C"
from ( select array_agg("Type"::text) as "Types"
from "B"
group by "UserId"
) t
;
The idea is that first we use a subquery that produces, for each user, an array containing his/her types; the outer query then just counts the arrays that contain each set of types.
You can see it in action at http://sqlfiddle.com/#!15/cbb45/1. (I've also included there a modified version of the subquery, to help you see how it works.)
Some relevant PostreSQL documentation:
array_agg
)@>
)If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With