Search code examples
mysqldatabasecountmariadbgroup-concat

SQL using group_concat with count of each concatenated item rather total count in a row

MYSQl - Count of elements inside Group_Concat

I rewrote it as this fiddle, but its giving error.

Anyone can help?

Solution

  • As far as concerns, to solve the error and get the correct result, you just need to return the CUSTOMERID from the aggregated subquery (the column is already part of the GROUP BY clause).

    Updated query:

     SELECT SELLER, 
       COUNT(*) as COUNT_OF_CUSTIDS,
       GROUP_CONCAT(CUSTOMERID, ' (', cnt, ')' SEPARATOR '|') AS CUSTIDS
FROM (
    SELECT SELLER, CUSTOMERID, COUNT(*) as cnt
    FROM docs
    GROUP BY SELLER, CUSTOMERID
) t
GROUP BY SELLER
HAVING COUNT(*) > 1

    Returns:

    | SELLER | COUNT_OF_CUSTIDS | CUSTIDS              |
| ------ | ---------------- | -------------------- |
| S1     | 3                | C2 (2)|C1 (2)|C3 (3) |
| S2     | 3                | C3 (1)|C2 (1)|C1 (2) |

    Demo on DB Fiddle