CODEWITHSUNDEEP
sqlpostgresql
Amil Osmanli
Amil Osmanli

Reputation: 135

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 | 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 |
+---------+---------+

Upvotes: 9

Views: 943

Answers (2)

John
John

Reputation: 213

Maybe I am interpreting this wrong, but I think you can do a pretty simple case statement in your select statement, but rather than count do a SUM:

SELECT SUM(CASE b.Types WHEN 'A' THEN 1 ELSE 0) as COUNT_A,
       SUM(CASE b.Types WHEN 'B' THEN 1 ELSE 0) as COUNT_B
FROM  A 
JOIN B
ON A.id = B.user_id
WHERE b1.type = 'A' or b2.type = 'B'

Upvotes: 0

ruakh
ruakh

Reputation: 183251

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:

Upvotes: 7

Related Questions