MySQL查询获取每个公司的客人总数

Question

select 
    first_name,
    last_name,
    c.name as company_name,
    sc.`date` as screening_date
from
    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
where 
    sdg.attending = 1
and  
    screening_date_id = 1
group by
    first_name,
    last_name

结果:

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

我的查询返回该结果集。

现在，我想查看另一列，其中包含每家公司的客人总数。在本例中，我们有 2 位来自 WBTDT 的客人，因此应该显示total_guest = 2

有人可以帮助我吗？

谢谢

Answer 1

实现此目的的一种方法是在相关子查询中获取每个公司的计数，所以也许这就是您想要的？

select 
    first_name,
    last_name,
    c.name as company_name,
    sc.date as screening_date,
    (
       select count(*) from 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
       where 
          screening_date_guest.attending = 1
       and  
          screening_date_id = 1 and company_id = c.id
    ) total_guests
from
    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
where 
    sdg.attending = 1
and  
    screening_date_id = 1
group by
    first_name,
    last_name,
    c.id,
    c.name,
    sc.date