Multi COUNT in one SELECT statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Multi COUNT in one SELECT statement

Author

Topic

weitzhandler
Yak Posting Veteran

64 Posts

Posted - 2008-12-01 : 23:18:17

say I have a table with the following schema:
UniqueID, State, Item

some states have many values, and other have no values at all;
I want to select this schema:
State, Count

e.g. I want the select to return a table that contains the state and how many records are registered with it.

I hope I'm clear.

then I want to do some order or some conditional things (e.g. where count > 5).

thank you very much!

Shimi

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-01 : 23:23:36

select state,count(*) as CountofStates
from table
group by state
having count(*)>5

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-01 : 23:24:51

quote:
Originally posted by sodeep

select state,count(*) as CountofStates
from table
group by state
having count(*)>5

You can put
order by state as well.

thiyagu_rind
Starting Member

46 Posts

Posted - 2009-01-08 : 00:53:06

Hi Dear,

I hope you can also use the below query to achieve your goal.

Query:
select state,sum(1) as CountofStates
from table
group by state
having sum(1)>5
order by state

Regards
Thiyagarajan
www.sqlhunt.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources