CODEWITHSUNDEEP
sqlms-access
jcmaad
jcmaad

Reputation: 43

Counting unique combinations

I have a table in the following format:

Input

And I am trying to find a way to count different combinations of the categories. Here is the output I am looking for:

Output

I tried grouping the Category field, but I cannot figure out a way to create a unique combination. Screenshots are in excel, simply for illustration purposes. Actual data is in ms-access.

Upvotes: 0

Views: 100

Answers (1)

forpas
forpas

Reputation: 164099

First get the unique pairs in a subquery and then join twice to the table to group and count:

select 
  t1.Category & '+' & t2.Category as Category, count(*) as Count
from (
  select t1.category as cat1, t2.category as cat2
  from tablename as t1, tablename as t2
  where not (t1.id = t2.id and t1.category = t2.category) and (t1.category < t2.category)
  group by t1.category, t2.category
) as t, tablename as t1, tablename as t2 
where t1.category = t.cat1 and t2.category =  t.cat2 and t1.id = t2.id
group by t1.Category & '+' & t2.Category

Upvotes: 2

Related Questions