mysql - Using Count with Group Concat -

mysql - Using Count with Group Concat -

fiddle example

i have page lists multiple polls , of vote options table vote_option. utilize table user_vote store users' vote records. want retrieve table vote result each vote alternative in each poll. can show me how utilize group_concat select count(*) user_vote grouping product_id result this:

poll_title vote_option img vote_count <---how column title product a,product b,product c imga,imgb,imgc 2,0,1 title b product a,product b,product d imga,imgb,imgd 0,1,0 title c product d,product e imgd,imge 1,0

table schema:

create table poll (`poll_id` int,`title`varchar(30)) ; insert poll (`poll_id`,`title`) values (1,'title a'), (2,'title b'), (3,'title c') ; create table product (`product_id` int,`product_name` varchar(30),`img`varchar(30)) ; insert product (`product_id`,`product_name`,`img`) values (1,'product a','imga'), (2,'product b','imgb'), (3,'product c','imgc'), (4,'product d','imgd'), (5,'product e','imge') ; create table vote_option (`poll_id` int,`product_id` int) ; insert vote_option (`poll_id`,`product_id`) values (1,1), (1,2), (1,3), (2,1), (2,2), (2,4), (2,2), (3,4), (3,5) ; create table user_vote (`poll_id` int,`product_id` int,`user_id` int) ; insert user_vote (`poll_id`,`product_id`,`user_id`) values (1,1,1), (1,1,2), (1,3,2), (2,2,1), (3,4,1) ; create table user (`user_id` int,`user_name` varchar (30)) ; insert user (`user_id`,`user_name`) values (1,'tom'), (2,'sally') ;

you need aggregate info first @ more granular level counts, , re-aggregate @ poll level:

select title, group_concat(product_name) products, group_concat(img) imgs, group_concat(cnt) cnt (select p.poll_id, p.title, pr.product_name, img, count(distinct uv.user_id) cnt vote_option v inner bring together poll p on p.poll_id = v.poll_id inner bring together product pr on pr.product_id = v.product_id left bring together user_vote uv on uv.product_id = v.product_id , uv.poll_id = v.poll_id left bring together user u on uv.user_id = u.user_id grouping p.poll_id, p.title, pr.product_name, img ) p grouping title limit 0, 25;

note can set info single list using like:

select title, group_concat(product_name, ',', img, ',', cnt separator '; ')

and using order by clause in group_concat(), can have highest vote-getter first in list.

mysql sql database count