sql query help 2

[MikeT]

Is it possible to create query which will report how many instances of a column values appear in the table?

Lets say I've a got a table with a column of states. I'd like to create a query that has a line for each state found in the table along with *how many* times it was found in the table.

Like
SELECT DISTINCT(statename)FROM states
would give me the name, but I also need to know how many times each state appears in the table.

Is this possible?

 

[JayKusch]

SELECT DISTINCT(statename),Count(statename) FROM states


Thanks

J. Kusch

 

[JamesLean]

SELECT statename, COUNT(*)
FROM states
GROUP BY statename
ORDER BY statename

--James

 

[JayKusch]

Sorry ...

SELECT DISTINCT(statename) as StateName,
Count(statename) as CountFoundForState
FROM states
GROUP BY statename

Thanks

J. Kusch

 

[JamesLean]

FYI, you don't need to use DISTINCT here as you will automatically get the distinct states by virtue of the GROUP BY.

--James

 

[MikeT]

Thanks guys, those did the trick!