MySQL查询、计数问题

Question

谢谢大家 - 解决了！

我有以下代码：

SELECT region, division, SUM(staff_count) AS Total_Staff, 
(SELECT COUNT(region) FROM tresults_xyc WHERE division = 'Central' GROUP BY region) AS Total_Responses
FROM  `trespondent_xyc` 
WHERE division = 'Central'
GROUP BY region

它返回以下内容：

region  division    Total_Staff Total_Responses
1       Central     212         8
2       Central     168         8
3       Central     164         8
4       Central     180         8

列区域、部门和 Total_Staff 中包含的信息是正确的。

但是，Total_Responses 列不是 - 它显示的是该部门而不是每个区域的响应总数。

有人可以建议吗？

预先感谢，

荷马。

Answer 1

你必须这样做：

SELECT region, division, SUM(staff_count) AS Total_Staff, 
       (
           SELECT COUNT(*) 
           FROM tresults_xyc t2
           WHERE t2.region = t1.region
       ) AS Total_Responses
FROM  `trespondent_xyc` t1
WHERE division = 'Central'
GROUP BY region

Answer 2

尝试

SELECT region, division, SUM(staff_count) AS Total_Staff, (SELECT COUNT(region) FROM tresults_xyc WHERE division = 'Central' and region = xx.region GROUP BY region) AS Total_Responses
FROM  `trespondent_xyc` xx 
WHERE division = 'Central'
GROUP BY region