问题:
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:
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:
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 JOIN
ing to tables of COUNT
s:
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;