mysql count items in group_concat

Question

I am having a trouble with using GROUP_CONCAT in MySQL My tables g0 as follows:

ID  Age Sex
-------------
1   16  Male
2   18  Female
3   16  Male
4   18  Female
5   16  Male

But I need the table to look like

ID        count
1,3,5       3
2,4         2

I tried this query:

SELECT GROUP_CONCAT(
CONCAT(cnt)) cnts FROM 
(SELECT COUNT(ID) as cnt FROM g0  GROUP BY Age , Sex order by ID Desc) ;

But I get this error message:

1248. Every derived table must have it's own alias

Purlieu

Which part of the error message is not clear? It literally tells you what is wrong. – Sfax

Answer 1

There's no need to have the count inside group_concat - just select it as a different item with the same group by expression:

SELECT   GROUP_CONCAT(id), COUNT(*)
FROM     g0
GROUP BY age, sex
ORDER BY 1 DESC

Mandal

Answer 2

Add alias AS cnts;

SELECT GROUP_CONCAT(
CONCAT(cnt)) cnts FROM 
(SELECT COUNT(ID) as cnt FROM g0  GROUP BY Age , Sex order by ID Desc)  AS cnts;

Parkins