Search code examples
mysqldatabasemariadbgroup-concatmulti-table

mysQL group_concat from multi table and count

I tried to convert single table to multi table extraction single table which is working perfect but multi table extraction is giving error. please any one can help

db-fiddle single table which is working perfect https://www.db-fiddle.com/f/mTHmv2idQwkdPZSqmRPi2Z/4

but whats wrong in this multi table i made??? https://www.db-fiddle.com/f/eUAUt53neNMBsnP1QxjzGJ/5

i expect below output same as fiddle i mention for single table. you can check the fiddle .

|---------------------|------------------|
|      SELLER         |    status        | 
|---------------------|------------------|
|          S1         |C3 :3,C1 :2,C2 :2 | 
|---------------------|------------------|
|          S2         |C3 :1,C1 :2,C2 :1 |
|---------------------|------------------|
Solution

  • The reason you are getting more records than you are expecting is because of the multiple joins that you have in your query.

    Try the following. This should return to you just the:

    select seller, group_concat(cid,' :', cnt  SEPARATOR ',') 
from
(SELECT  cases.SELLER, cases_cstm.customerid as cid, COUNT(*) as cnt FROM 
cases, cases_cstm WHERE cases.id=cases_cstm.id_c GROUP BY  cases.SELLER, 
cases_cstm.CUSTOMERID) q
group by seller;

    If you need the count of the customer ids you should include count(cid) to your select clause. Hope this helps!