1

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?

Joachim Isaksson
  • 176,943
  • 25
  • 281
  • 294
arth81
  • 229
  • 5
  • 17

1 Answers1

2

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.

Community
  • 1
  • 1
Joachim Isaksson
  • 176,943
  • 25
  • 281
  • 294
  • Joachim, do you know any equivalent of GROUP_CONCAT in tsql? It seems that there is no any. – arth81 Apr 18 '14 at 07:50
  • 1
    @arth81 Just added a TSQL version. – Joachim Isaksson Apr 18 '14 at 07:53
  • Joachim, I ve complicated situation a bit by adding another group : "decision". Now there should be two unique groups of (A,B): yes, no. I ve try to modify your code by adding: m.decision = mt.decision in where close and group but it seems that the core issue is depend on FOR XML PATH('') which I didn't catch fully. Could you explain me this? do you have any ideas how to modify this query? – arth81 Apr 18 '14 at 08:55
  • @arth81 Are all decisions always the same within a group? – Joachim Isaksson Apr 18 '14 at 08:58