mysQL group_concat from multi table and count

Question

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 |
|---------------------|------------------|

updated. I tried initially ...there was some error and i thought may be format is not right but it let me add out put this time. — programmergeeky, Mar 18 '19 at 22:40
@raj currently i am getting out as in this fiddle https://www.db-fiddle.com/f/eUAUt53neNMBsnP1QxjzGJ/2 which is wrong — programmergeeky, Mar 18 '19 at 22:51

score 0 · Accepted Answer · answered Mar 18 '19 at 23:57

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!

Working now !!!! Thanks Raj. Oh i got the error, i had to use different variable at topmost select and lower most group by ...and that was mistake. I was using database table variable name....cases.SELLER when i had to use seller. — programmergeeky, Mar 19 '19 at 05:12

mysQL group_concat from multi table and count

1 Answers1