MySQL Query, Count issue

Question

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.

Your inner query has no any reference to region thus counts values for all. — zerkms
– zerkms, Commented Jul 4, 2011 at 12:00

Cyril Gandon · Accepted Answer · 2011-07-04 12:17:14Z

2

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

edited Jul 4, 2011 at 12:17

answered Jul 4, 2011 at 12:00

Cyril Gandon

17.1k15 gold badges84 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

zerkms Over a year ago

The second query will produce different results

Homer_J Over a year ago

Indeed, the first query worked perfectly. The second produced spurious results!

Haim Evgi · Accepted Answer · 2011-07-04 12:00:43Z

0

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

answered Jul 4, 2011 at 12:00

Haim Evgi

126k46 gold badges222 silver badges226 bronze badges

1 Comment

Homer_J Over a year ago

Works, except it brings back NULLS, the code before (first query) brings me back zeros where there are no responses.

Collectives™ on Stack Overflow

MySQL Query, Count issue

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related