Using COUNT in GROUP_CONCAT

count, group-concat, mysql, sql

Solution

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.

Problem

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?

Original source