MYSQl - Count of elements inside Group_Concat

Question

I'm quite new to MYSQL and would need a little help in the group_concat statement. I have the below table

Seller Merchant CustomerID
S1     M1       C1
S1     M1       C1
S1     M1       C2
S1     M1       C3
S1     M1       C4
S2     M2       C5
S2     M2       C6
S3     M3       C6

For the combination of same seller and merchant, all the items which has different customerIDs along with the count of how many times it is repeated.

I'm able to derive count of unique customer IDS using group_concat but not able to get the count.

SELECT * , LENGTH(CUSTIDS) - LENGTH(REPLACE(CUSTIDS,',',''))+1 AS COUNT_OF_CUSTIDS
FROM (SELECT SELLER, MERCHANT, GROUP_CONCAT(CUSTOMERID SEPARATOR '|') AS CUSTIDS
FROM TABLE
GROUP BY SELLER, MERCHANT
HAVING COUNT(DISTINCT CUSTOMERID ) >1 
)

which gives me the below result

Seller Merchant CustomerID    COUNT_OF_CUSTIDS
S1     M1       C1,C2,C3,C4   4
S2     M2       C5,C6         2

whereas I would want the below

Seller Merchant CustomerID                 COUNT_OF_CUSTIDS
S1     M1       C1(2),C2(1),C3(1),C4(1)    4
S2     M2       C5(1),C6(1)                2

Answer 1

You need to first aggregate at the seller/merchant/customerid level to get the count. Then you can continue with your aggregation:

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