CODEWITHSUNDEEP
mysqlsql
user3017615
user3017615

Reputation: 569

Select unique entries of column and filter these by unique entries of another one

I have the following hypothetic table:

col_1 | col_2
a      1
b      1
c      1
d      2
e      2
a      3
b      3
c      3

What I want is the number of unique entries in column 1 (5 - a, b, c, d, e) filtered by the unique entries of column 2. The output should be 2 because 

col_1 | col_2
a      1,3
b      1,3
c      1,3
d      2
e      2

is

col_1 | col_2
a,b,c   1,3
d,e     2

I put no value of the entries. I only want the number of output rows. What can I do? I tried something with DISTINCT and GROUP BY but it brought no success.

Thank you!

Upvotes: 0

Views: 56

Answers (1)

Mureinik
Mureinik

Reputation: 311338

group_concat has the basic functionality you need, and also supports distinct-ness. You just need to rig together a couple of sub-queries:

SELECT   GROUP_CONCAT(DISTINCT col_1) AS col_1, col_2
FROM     (SElECT   col_1, GROUP_CONCAT(DISTINCT col2) AS col_2
          FROM     my_table
          GROUP BY col_1) t
GROUP BY col2

Upvotes: 1

Related Questions