Select multiple counters from one database table in one access to sql command

Question

I have one table in the following format:

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

I am looking for one query that will give 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 get:

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

Here is the code I'm 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; 

Although this and other forums were very helpful, I cannot understand what I'm doing wrong. PLEASE NOTE: I use Access so that CASE WHEN solutions do not work. Thanks for any advice.

Answer 1

It seems like this could be a problem caused by not using table aliases. Since you are executing subqueries in the same table that the external SELECT uses and does not give the external table an alias, both conditions in the WHERE subquery consist only of using the data in the subquery, the query.

In other words, when you write:

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

He does not know anything about an external request.

Try the following:

 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 

Answer 2

Aiias answer explains why your current query is not working, but I thought I would indicate that your assumption that you cannot use CASE WHEN solutions is partly right, yes you cannot use CASE WHEN , but it doesn't mean you need correlated subqueries. You can simply use:

 SELECT mytable.STATE, SUM(IIF(mytable.survey_answer = 'low', 1, 0) AS low, SUM(IIF(mytable.survey_answer = 'moderate', 1, 0) AS moderate, SUM(IIF(mytable.survey_answer = 'high', 1, 0) AS high FROM mytable GROUP BY mytable.state;