Count

[goslincm]

I have this simple query:

SELECT DISTINCT [Registration AACO 2007].State
FROM [Registration AACO 2007]
GROUP BY [Registration AACO 2007].State;

But I need it to tell me also a count of the number of different States. I don't know how to get that into this??

 

[r937]

[blue]select count(*) from ( [/blue]
SELECT DISTINCT  [Registration AACO 2007].State 
FROM [Registration AACO 2007]
GROUP BY [Registration AACO 2007].State 
[blue])[/blue]

r937.com | rudy.ca

 

[goslincm]

So if I then wanted to add the field [aapfco] to the mix, which is a yes/no field and I want to see how many records marked 'yes' in [aapfco] and what is the count of those marked yes in terms of the different states.

So if I have a record with the state = Ohio and the [aapfco] field marked yes, and another record with the State Michigan and the [aapfco] field marked yes, I want my query to show:

Michigan 1
Ohio 1

I tried this but it is not working:

SELECT Count(*)
FROM ([Select Distinct [registration aaco 2007].[state]
FROM [registration aaco 2007]
WHERE [aapfco]= yes]
GROUP BY [state], [aapfco]);

 

[r937]

SELECT [state], Count(*)
FROM [registration aaco 2007]
WHERE [aapfco]= yes
GROUP BY [state]

r937.com | rudy.ca

 

[goslincm]

But that does not give me a count of the distinct states does it?

 

[r937]

no, it doesn't

however, you did say that you wanted the query to show:

Michigan 1
Ohio 1

which is what my latest query shows

r937.com | rudy.ca

 

[goslincm]

BUT as states get added, I need the count to change. Sorry if I'm not being clear

 

[r937]

add a couple of states now, and see ;-)

r937.com | rudy.ca

 

[goslincm]

Brillant, sorry about that.

 

[goslincm]

r937,

So my user tells me thats still what is needed. Instead of the way its showing, they need to see for those marked YES for AAPFCO, the COUNT of number of different states. So in the previous example, we need a query to return a State count of 2

 

[r937]

okay, i understand

so go back to post #2 and add the WHERE clause



r937.com | rudy.ca

 

[goslincm]

Won't work like this:

select count(*) from (
SELECT DISTINCT [Registration AACO 2007].State
FROM [Registration AACO 2007]
GROUP BY [Registration AACO 2007].State
WHERE [registration aaco 2007].[aapfco]=yes);

 

[r937]

remove the GROUP BY

i should have spotted this right at the outset, sorry

in this type of query, GROUP BY and DISTINCT are redundant

r937.com | rudy.ca

 

[goslincm]

r937....thanks for the help its now working as requested.