Count rows that have the same set of related records

Question

This is probably pretty simple but I can't figure this out. Here are the tables I'm working with.

table_a

id  other_data
-------------
1      blah
2      foo
3      bar

table_b

ref_a  ref_c
-------------
1      1
1      2
2      3
3      3

table_c

id   name
----------
1    TestA
2    TestB
3    TestC

What I'm trying to get is something like this where I'm counting the number of rows (table_a) that have the same set of children (table_b). I also want to be able to get related data from another table (The name from table_c).

TestA,TestB 1
TestC       2

I know it probably uses Group By and GROUP_CONCAT but I can't get this to work.

I tried this but it doesn't work.

SELECT GROUP_CONCAT(DISTINCT table_c.name separator ', ') as 'combo_text', COUNT(DISTINCT table_a.id) FROM table_a INNER JOIN table_b on table_a.id = table_b.ref_a INNER JOIN table_c on table_c.id = table_b.ref_c GROUP BY table_b.ref_a

Please format your question better (your example data and example output) — apartridge
Can you please also edit your question to include what you tried that didn't work, per Stack Overflow's good question guidelines? — Derek

Tin Tran Tin Tran · Accepted Answer · 2013-12-13T22:28:48

SELECT a.id, count(a.id) as count, GROUP_CONCAT(name) as names
FROM table_a a
JOIN table_b b ON (a.id = b.ref_a)
JOIN table_c c ON (b.ref_c = c.id)
GROUP BY a.id

sqlFiddle demo

based on your result you want something like this

SELECT names, count(count) as count FROM
  (SELECT a.id, count(a.id) as count, GROUP_CONCAT(name) as names
   FROM table_a a
   JOIN table_b b ON (a.id = b.ref_a)
   JOIN table_c c ON (b.ref_c = c.id)
   GROUP BY a.id
  )T1
GROUP BY names

sqlFiddle demo

Count rows that have the same set of related records

1 Answers