显示列的不同值的计数 [英] Show count of columns distinct values

问题描述

大家好！



我想首先感谢所有花时间查看此主题并尝试提供帮助的人。



我会简短，并切入点：



我在MS Access中有一个表，它包含2列兴趣 - 县和TGTE（地热能类型）。列TGTE是VARCHAR类型，它可以有两个值中的一个，以便更容易让它说它是L或H.



我需要创建SQL查询这显示了一个下面描述的结果（代码标签是为了更清晰的外观）：



贝娄是表的一部分：



<前lang =c ++>县| TGTE | ......其他领域|
--------------------
首先| L |
首先| L |
首先| H |
第二个| H |
第三| L |

我需要一个结果查询，显示每个县的不同TGTE的数量，如下所示：

县| TGTE = L | TGTE = H | 
 ---------------------------------------------- -  
首先|  2  |  1  | 
第二个|  0  |  1  | 
第三|  1  |  0  | 

如何创建显示上面描述的期望结果？

解决方案

我有像下面这样的查询，它有效：

 SELECT县
，sum（ switch （ТGTE= '  L'， 1 ，TGTE = '  H'， 0 ））为[L_Count] 
，sum（ switch （ТGTE= '  H'， 1 ，TGTE = '  L'， 0 ））as [H_Count] 
 FROM MyTable 
 GROUP BY 
 County; 
 

当我通过ADO和MS Access 2007运行时，一切正常。

几个选项...要么创建一个查询按县和TGTE分组并计算ID字段，然后运行交叉表查询...



或...



为两种类型的TGTE中的每一种创建一个子查询，按县和TGTE的where子句，然后有一个主查询返回县，连接到两个子查询（通过左连接）以获得TGTE第一列和第二列。

关注：

http://office.microsoft .com / en-in / access-help / make-summary-data-easy-to-using-a-crosstab-query-HA010229577.aspx [ ^ ]

Hello everyone!

I would like to start by saying thanks to everyone who takes some time to view this thread and try to help.

I will be brief, and cut to the point:

I have a table in MS Access, it contains 2 columns of interest- County, and TGTE (Type Of Geothermal Energy ). Column TGTE is of type VARCHAR and it can have 1 of two values, to make it easier let's say it is either L or H.

I need to create SQL query that shows a result which is described bellow ( code tags are for clearer look ):

Bellow is the part of the table:

County | TGTE | ... other fields |  
-------------------- 
First  |   L  |
First  |   L  |
First  |   H  |
Second |   H  |
Third  |   L  |

I need a resulting query that shows the count of distinct TGTE in every County like this:

County | TGTE = L | TGTE = H |  
------------------------------------------------  
First  |    2     |     1    |
Second |    0     |     1    |
Third  |    1     |     0    |

How can I create query that displays the desired result described above ?

解决方案

I have the query like bellow, and it works:

SELECT  County
         ,sum( switch( ТGTE = 'L', 1, TGTE = 'H', 0 ) ) as [L_Count]
         ,sum( switch( ТGTE = 'H', 1, TGTE = 'L', 0 ) ) as [H_Count]
 FROM    MyTable
 GROUP BY
         County;

Everything works fine, when I run it through ADO and MS Access 2007.

Couple of options... either create a query to group by County and TGTE and count the ID field, then run a crosstab query over that...

or...

Create a subquery for each of the two types of TGTE, group by county and where clause on the TGTE, then have a main query to return the County, attached to the two subqueries (by a left join) to get TGTE column one and two.

Follow:
http://office.microsoft.com/en-in/access-help/make-summary-data-easier-to-read-by-using-a-crosstab-query-HA010229577.aspx[^]

这篇关于显示列的不同值的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！