Search code examples
mysqlsqlconcatenationgroup-concat

CONCAT() inside GROUP_CONCAT() with count

I am trying to get results with group_concat, concat and count functions in MySQL but it gives me error. here is my table

enter image description here

First, when I try to get count and status with concat, it works fine.

$query="SELECT CONCAT(`status`,':',count(status)) FROM `mytable` GROUP BY status"

output:
Hold:2
Completed:3
Cancelled:2

It's all fine till here. Now I want this output in one row. So I tried using GROUP_CONCAT().

$query="SELECT GROUP_CONCAT(CONCAT(`status`,':',count(status)) SEPARATOR ',') as rowString FROM `mytable` GROUP BY status"

but now its giving me error " Invalid use of group functions"

Note: the same query works if I replace count(status) with some other field from table ( without count). The count() function is causing some problem when used in this manner.

Desired Output

Hold:2,Completed:3,Cancelled:2

Appreciate your help.

Solution

  • You cannot nest aggregation functions (count() is in the arguments to group_conat()). One solution is to select from a nested subquery.

    SELECT group_concat(status, ':', count SEPARATOR ',') rowstring
       FROM (SELECT status,
                    count(*) count
                    FROM mytable
                    GROUP BY status) x;