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.

Aiias · Accepted Answer · 2013-06-27 18:20:39Z

5

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

answered Jun 27, 2013 at 18:20

Aiias

4,7481 gold badge20 silver badges34 bronze badges

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

Comments

GarethD · Accepted Answer · 2013-06-27 18:46:35Z

2

Aiias answer explains why your current query is not working, but I thought I'd point out that your assumption that you can't use CASE WHEN solutions is only partly right, yes you can't use CASE WHEN but that doesn't mean you need correlated subqueries. You could 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;

answered Jun 27, 2013 at 18:46

GarethD

70k11 gold badges90 silver badges130 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related