title: MYSQl - Count of elements inside Group_Concat
body: <p>I'm quite new to MYSQL and would need a little help in the group_concat statement. I have the below table</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>I'm able to derive count of unique customer IDS using group_concat but not able to get the count. </p>
  
  <pre><code>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 ) &gt;1 
  )
  </code></pre>
  
  <p>which gives me the below result </p>
  
  <pre><code>Seller Merchant CustomerID    COUNT_OF_CUSTIDS
  S1     M1       C1,C2,C3,C4   4
  S2     M2       C5,C6         2
  </code></pre>
  
  <p>whereas I would want the below</p>
  
  <pre><code>Seller Merchant CustomerID                 COUNT_OF_CUSTIDS
  S1     M1       C1(2),C2(1),C3(1),C4(1)    4
  S2     M2       C5(1),C6(1)                2
  </code></pre>