在一个sql命令访问中从一个数据库表中选择多个计数 [英] Select multiple counts from one database table in one sql command access

问题描述

我只有一个表格，格式如下:

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;

尽管这个论坛和其他论坛都非常有帮助，但我无法弄清楚自己在做错什么.请注意:我正在使用Access，所以在解决方案不起作用的情况下.谢谢您的任何建议.

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.

推荐答案

似乎是由于不使用表别名而引起的问题.由于您要在外部SELECT正在使用的同一表上执行子查询，并且没有为外部表提供别名，因此子查询WHERE中的两个条件都仅使用子查询中的数据.查询.

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.

尝试一下:

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

这篇关于在一个sql命令访问中从一个数据库表中选择多个计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！