sql组合两个count outome

vcirk6k6 于 2021-07-29 发布在 Java

关注(0)|答案(3)|浏览(273)

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

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

表结构
代理人

客户

sql mysql

来源：https://stackoverflow.com/questions/62709974/sql-combine-two-count-outomes

3条答案

按热度按时间

flvlnr441#

你必须数清楚的数字 agent_code s和 cust_code 学生：

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

赞(0）回复(0）举报 2021-07-29

zqry0prt2#

请使用下面的查询，

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;

赞(0）回复(0）举报 2021-07-29

wvmv3b1j3#

我不知道它是否有效，但你能把这些数据和结构放到：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

赞(0）回复(0）举报 2021-07-29

我来回答

sql组合两个count outome

3条答案

相关问题

热门标签

最新问答