How to combine aggregated queries with different joins?

问题: I've been looking at this SO post and this jooq.org post, trying to figure out how to do my combined aggregations in MySQL, but not having much luck. Here are my 2 queri...

问题:

I've been looking at this SO post and this jooq.org post, trying to figure out how to do my combined aggregations in MySQL, but not having much luck.

Here are my 2 queries:

select a.IsTestAgency, a.ID, a.AgencyName, a.CreateDate, count(*) AS Agents
from Users u
join Agencies a
on u.AgencyID = a.ID
group by a.IsTestAgency, a.ID, a.AgencyName, a.CreateDate
order by a.IsTestAgency, a.AgencyName;

Results:

enter image description here

and:

select a.IsTestAgency, a.ID, a.AgencyName, a.CreateDate, count(*) AS Certs
from Certificates c
join Agencies a
on c.AgencyID = a.ID
group by a.IsTestAgency, a.ID, a.AgencyName, a.CreateDate
order by a.IsTestAgency, a.AgencyName;

Results:

enter image description here

You can see that the columns and columns' datatypes match. I'd like to combine these into a single query and show the Agents count and the Certs count side-by-side, since those are the only 2 column values that are different in the result sets.

How is it done?


回答1:

You could do this by JOINing to tables of COUNTs:

select a.IsTestAgency, a.ID, a.AgencyName, a.CreateDate, u.Agents, c.Certs
from Agencies a
join (select AgencyID, COUNT(*) as Agents from Users group by AgencyID) u on u.AgencyID = a.ID
join (select AgencyID, COUNT(*) as Certs from Certficates group by AgencyID) c on c.AgencyID = a.ID
order by a.IsTestAgency, a.AgencyName;

This removes the need to group by in the top query and also saves having to do two subquery counts for each row of the output.


回答2:

Is this what you want?

select a.*, 
       (select count(*)
        from users u
        where u.AgencyID = a.ID
       ) as users_count,
       (select count(*)
        from Certificates c
        where c.AgencyID = a.ID
       ) as certificates_count
from Agencies a
order by a.IsTestAgency, a.AgencyName;
  • 发表于 2019-03-21 09:42
  • 阅读 ( 157 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除