sql - MySQL Query get total number of guests per company -

select      first_name,     last_name,     c.name company_name,     sc.`date` screening_date     guests g inner join      user_guest_group ugs on ugs.guest_id = g.id inner join      companies c on c.id = g.company_id inner join     screening_date_guest sdg on sdg.guest_id = g.id inner join     screening_dates sc on sc.id = sdg.screening_date_id      sdg.attending = 1 ,       screening_date_id = 1 group     first_name,     last_name 

results:

peter, m, bell media (ctv), 2015-05-18 00:00:00 adam, d, highway entertainment, 2015-05-18 00:00:00 todd, f., multichoice, 2015-05-18 00:00:00 john, d, talpa, 2015-05-18 00:00:00 maria, f, uk tv, 2015-05-18 00:00:00 john, l, wbdtd, 2015-05-18 00:00:00 albert, p, wbdtd, 2015-05-18 00:00:00 

my query returns resulset.

now, want see column total guests per company. in case, have 2 guests wbtdt should total_guest = 2

can me ?

thanks

one way count per company in correlated subquery, maybe want?

select      first_name,     last_name,     c.name company_name,     sc.date screening_date,     (        select count(*) guests        inner join            user_guest_group  on user_guest_group.guest_id = guests.id        inner join            companies  on companies.id = guests.company_id        inner join           screening_date_guest  on screening_date_guest.guest_id = guests.id        inner join           screening_dates  on screening_dates.id = screening_date_guest.screening_date_id                   screening_date_guest.attending = 1        ,             screening_date_id = 1 , company_id = c.id     ) total_guests     guests g inner join      user_guest_group ugs on ugs.guest_id = g.id inner join      companies c on c.id = g.company_id inner join     screening_date_guest sdg on sdg.guest_id = g.id inner join     screening_dates sc on sc.id = sdg.screening_date_id      sdg.attending = 1 ,       screening_date_id = 1 group     first_name,     last_name,     c.id,     c.name,     sc.date