MySQL Query, Count issue

THANKS EVERYONE - SOLVED!

I have the following code:

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

It brings back the following:

region  division    Total_Staff Total_Responses
1       Central     212         8
2       Central     168         8
3       Central     164         8
4      开发者_高级运维 Central     180         8

The information contained in the colomns region, division, and Total_Staff are correct.

However, the Total_Responses colomn is not - that is showing the total number of responses for the Division and not each region.

Can anyone advise?

Thanks in advance,

Homer.

---

You have to do this :

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

---

try

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