Show count of columns distinct values

Question

0.00/5 (No votes)

See more:

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:

C++

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:

C++

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 ?

Posted 5-Aug-13 13:16pm

MyOldAccount

Add a Solution

Comments

Mohibur Rashid 5-Aug-13 20:55pm

try stored procedure

3 solutions

Solution 1

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.

Posted 5-Aug-13 16:09pm

_Damian S_

Comments

MyOldAccount 6-Aug-13 19:02pm

I am inexperienced with SQL.
Although I vaguely understand what you're talking about, I could really use a code snippet, or a pseudo code.
Thank you for your suggested answer!

Solution 2

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

Posted 5-Aug-13 17:43pm

Kuthuparakkal

Comments

MyOldAccount 6-Aug-13 19:04pm

This is interesting, I will try it out and report my results as soon as possible!
Thank you for your suggested solution!

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MyOldAccount · Accepted Answer · 2013-08-06T13:01:00

I have the query like bellow, and it works:

C++

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.

Show count of columns distinct values

3 solutions

Solution 3

Solution 1

Solution 2

Add your solution here

Preview 0

Show count of columns distinct values

3 solutions

Solution 3

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us