CODEWITHSUNDEEP
sqlsql-servert-sql
arth81
arth81

Reputation: 229

Grouping pairs in sql

I ve following table:

id | group | decision | category
___ _______ _________
1  | 1111  | yes |  A
2  | 1111  | yes | B
3  | 2222  | no  | A
4  | 3333  | yes | A
5  | 3333  | yes | C
6  | 4444  | no  | A
7  | 4444  | no  | B

Now I'd like to count pairs and unique categories in the following way: (A,B) = 2, (A)=1, (A,C) = 1. Is it possible to write sql query which give such result?

Upvotes: 1

Views: 302

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180927

In MySQL, you can simply form all the groups using GROUP_CONCAT in a subquery, and count them in an outer query;

SELECT categories, COUNT(*) count
FROM (
  SELECT GROUP_CONCAT(category ORDER BY category) categories
  FROM mytable
  GROUP BY `group`
) z
GROUP BY categories;

An SQLfiddle to test with.

In TSQL, GROUP_CONCAT doesn't exist, so you can use a trick from here

SELECT categories, COUNT(*) count
FROM (
  SELECT 
    STUFF((SELECT ',' + mt.category
           FROM mytable mt
           WHERE m.[group] = mt.[group]
           ORDER BY mt.category
           FOR XML PATH(''), TYPE).
                 value('.', 'NVARCHAR(MAX)'), 1, 1, '') categories
  FROM mytable m
  GROUP BY [group]
) z
GROUP BY categories;

Another SQLfiddle.

Upvotes: 2

Related Questions