SQL合并两个计数组

Question

你好，任何关于如何实现以下目标的提示。

select GENDER, count(AGENT_CODE) as Total_Agents from Agents group by Gender

结果：

GENDER Total_Agents
F      4
M      8

这是：

select count(*) as Total_Customers, Agents.GENDER 
from Agents join Customer 
on Agents.AGENT_CODE = Customer.AGENT_CODE 
group by Agents.GENDER

结果：

Total_Customers GENDER
11              F
14              M

我尝试了以下几点：

select count(AGENT_CODE) as Count,Gender as Gender from Agents group by Gender
union
select count(*) as Count, Agents.GENDER from Agents join 
Customer on Agents.AGENT_CODE = Customer.AGENT_CODE
group by Agents.GENDER

结果：

Count Gender 
4     F
11    F 
8     M
14    M

即使这样也失败了

select count(Agents.AGENT_CODE) as Total_Agents, count(Customer.AGENT_CODE) as 
Total_Customers, Agents.GENDER 
from Agents join Customer on Agents.AGENT_CODE = Customer.AGENT_CODE 
group by Agents.GENDER;

Total_Customers Total_Agents Agents_Gender
       14           14             M
       14           11             F

预期输出:所有的表都是这样的：

Total_Agents Total_Customers Agents_Gender
8             14             M
4             11             F

表结构

• 代理

• 客户

Answer 1

您必须计算不同的agent_codes和cust_codes：

select  
  count(distinct a.agent_code) Total_Agents,
  count(distinct c.cust_code) Total_Customers,
  a.gender
from agents a inner join customer c 
on a.agent_code = c.agent_code
group by a.gender

Answer 2

请使用以下查询，

select count(Agents.AGENT_CODE) as Total_Agents, count(Customer.AGENT_CODE) as 
Total_Customers, Agents.GENDER 
from Agents join Customer on Agents.AGENT_CODE = Customer.AGENT_CODE 
group by Agents.GENDER;

Answer 3

我不知道它是否有效，但您能否将这些数据和结构放入：sqlfiddle.com，以便我们能够测试和查看结果，甚至在出错时修改我的答案。

SELECT
  (select count(AGENT_CODE) as Count from Agents group by Gender) as Total_Agents,
  (select count(*) as Count, Agents.GENDER from Agents 
     join Customer on Agents.AGENT_CODE = Customer.AGENT_CODE
      group by Agents.GENDER
  ) as Total_Customers, 
  GENDER
FROM Agents