Select multiple counts from one database table in one sql command access

Question

I have a single table in the following format:

STATE SURVEY_ANSWER  
NC high  
NC moderate  
WA high  
FL low  
NC high

I am looking for a single query that will get me the following result:

STATE HIGH MODERATE LOW 
NC 2 1 0  
WA 1 0 0  
FL 0 0 1

Unfortunately, these are the results I am getting:

STATE HIGH MODERATE LOW 
NC 3 1 1  
WA 3 1 1  
FL 3 1 1  

Here is the code I am using:

Select mytable.STATE,
(SELECT COUNT(*) FROM mytable WHERE mytable.survey_answer = 'low' and state = mytable.state) AS low,
(SELECT COUNT(*) FROM mytable WHERE mytable.survey_answer = 'moderate' and state = mytable.state) AS moderate,
(SELECT COUNT(*) FROM mytable WHERE mytable.survey_answer = 'high' and state = mytable.state) AS high,
FROM mytable
GROUP BY mytable.state;

While this and other forums have been very helpful I am unable to figure out what I am doing wrong. PLEASE NOTE: I am using Access so CASE WHEN solutions do not work. Thank you for any advice.

Answer 1

It looks like this may be an issue caused by not using table aliases. Because you are doing sub-queries on the same table that the outer SELECT is using and not giving the outer table an alias, both of the conditions in the WHERE of the sub-query are only using data in the sub-query.

In other words, when you write:

SELECT COUNT(*) FROM mytable WHERE mytable.survey_answer = 'low' and state = mytable.state

It doesn't know anything about the outer query.

Try this:

SELECT t1.STATE,
  (SELECT COUNT(*) FROM mytable t2 WHERE t2.state = t1.state AND t2.survey_answer = 'low') low,
  (SELECT COUNT(*) FROM mytable t3 WHERE t3.state = t1.state AND t3.survey_answer = 'moderate') moderate,
  (SELECT COUNT(*) FROM mytable t4 WHERE t4.state = t1.state AND t4.survey_answer = 'high') high,
FROM mytable t1
GROUP BY t1.state