Using COUNT in GROUP_CONCAT

Question

This is my table:

  id  | fk_company
-------------------
  1   |     2    
  2   |     2    
  3   |     2    
  4   |     4    
  5   |     4    
  6   |     11   
  7   |     11   
  8   |     11   
  9   |     12

The result I want should be string "3, 2, 3, 1" (count of items that belong to each company), because this is just part of my complex query string.

I tried to use this query:

SELECT GROUP_CONCAT(COUNT(id) SEPARATOR ", ")
FROM `table` GROUP BY fk_company;

But I got an error:

Error Number: 1111
Invalid use of group function

I have a feeling COUNT, MAX, MIN or SUM can't be used in GROUP_CONCAT. If so, do you know another way to do this?

Answer 1

You need to COUNT() with GROUP BY in an inner SELECT clause first and then apply GROUP_CONCAT();

SELECT GROUP_CONCAT(cnt) cnt
FROM (
    SELECT COUNT(*) cnt
    FROM table1
    GROUP BY fk_company
) q;

Output:

|   CNT   |
-----------
| 3,2,3,1 |

Here is SQLFiddle demo.

Answer 2

You can also achieve that by counting the number of commas (or whatever's your separator) in the GROUP_CONCAT:

SELECT (LENGTH(GROUP_CONCAT(DISTINCT fk_company))-LENGTH(REPLACE(GROUP_CONCAT(DISTINCT fk_company), ',', '')))
FROM `table`
GROUP BY fk_company