CODEWITHSUNDEEP
sqldatabasepostgresql
student248
student248

Reputation: 1

getting the count of unique combinations in columns of a sql table

Assuming you have a database with patrons table with the following columns and 100,000 rows:

CREATE TABLE patron (
  id       INT,
  name     VARCHAR(100),
  deptA    INT,
  deptB    INT,
  deptC    INT
 );

A few example rows are the following:

+----+------+-------+-------+-------+
| id | name | deptA | deptB | deptC |
+----+------+-------+-------+-------+
| 1  | Bob  |  1    |   1   | NULL  |
| 2  | Bill | NULL  |   2   | NULL  |
| 3  | Mike |  3    | NULL  | NULL  |
| 4  | John |  4    |   4   |   4   |
| 5  | Matt | NULL  | NULL  |   5   |
| 6  | Jack |  6    |   6   | NULL  |
| 7  | Sean |  7    |   7   |   7   |
| 8  | Adam |  8    |   8   |   8   |
+----+------+-------+-------+-------+

And I want a table like so (A = department A only, B = department B only , C = department C only, AB = department A and B, etc) :

+-----------+------------+
| Department| User Count |
+-----------+------------+
| A         |     40,121 |
| B         |     25,663 |
| C         |     13,925 |
| AB        |      6,253 |
| AC        |      5,870 |
| BC        |      5,123 |
| ABC       |      3,045 |
+-----------+------------+

A person would be considered to be not part of a department if the value for a department is NULL. For example, if I was only part of department A, the value of department A would be my id value, and the values for department B and C would be NULL.

What would be the query in SQL that does this? I'm lost on how to find the unique (distinct) combinations and make them their own column.

Upvotes: 0

Views: 247

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

You should do this with a single aggregation:

select concat_ws(',',
                 (case when deptA > 0 then 'A' end),
                 (case when deptB > 0 then 'B' end),
                 (case when deptC > 0 then 'C' end)
                ) as Department
        count(*)
from patron
group by Department
order by length(Department), Department;

Upvotes: 2

Kent Weigel
Kent Weigel

Reputation: 1178

select 'A' Department, count(id) UserCount from patron where deptA = id
union all
select 'B' Department, count(id) UserCount from patron where deptB = id
union all
select 'C' Department, count(id) UserCount from patron where deptC = id
union all
select 'AB' Department, count(id) UserCount from patron where deptA = id and deptB = id
union all
select 'AC' Department, count(id) UserCount from patron where deptA = id and deptC = id
union all
select 'BC' Department, count(id) UserCount from patron where deptB = id and deptC = id
union all
select 'ABC' Department, count(id) UserCount from patron where deptA = id and deptB = id and deptC = id

Upvotes: 0

joshweir
joshweir

Reputation: 5627

select "A" as Department, 
 (select count(1) from patron 
  where deptA is not null 
  and deptB is null 
  and deptC is null) as "User Count"
union all
select "B" as Department, 
 (select count(1) from patron 
  where deptA is null 
  and deptB is not null 
  and deptC is null) as "User Count"
union all
select "C" as Department, 
 (select count(1) from patron 
  where deptA is null 
  and deptB is null 
  and deptC is not null) as "User Count"
union all
select "AB" as Department, 
 (select count(1) from patron 
  where deptA is not null 
  and deptB is not null 
  and deptC is null) as "User Count"
union all
select "AC" as Department, 
 (select count(1) from patron 
  where deptA is not null 
  and deptB is null 
  and deptC is not null) as "User Count"
union all
select "BC" as Department, 
 (select count(1) from patron 
  where deptA is null 
  and deptB is not null 
  and deptC is not null) as "User Count"
union all
select "ABC" as Department, 
 (select count(1) from patron 
  where deptA is not null 
  and deptB is not null 
  and deptC is not null) as "User Count"

Upvotes: 0

Related Questions