State Count - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

State Count

Author

Topic

battle59
Starting Member

8 Posts

Posted - 2007-05-09 : 11:08:33

Hi All,

I ckecked other posts before posting, and I found one that applies but doesn't quite work for me. I have a database with Address records and want to know how many records are in each state.

Ex,

State Count
AL 26
CA 45
FL 16
NY 75

I tried using this previous post but changing to state if that helps:

Select Count(Name) as [Count], City
From Table
Group by City

Thanks in advance.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-09 : 11:11:10

So what did you get? What problem you are facing? Post some sample data and expected output.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

battle59
Starting Member

8 Posts

Posted - 2007-05-09 : 11:27:54

I received a count but no state column. I want to know how many records in my database are in what states.

This is the data I have:
ADDRESS ADDRESS2 CITY STATE ZIP
833 E LAS FLORES WAY SANTA MARIA CA 93454-6332
3102 COUNTRY CLUB DR RAPID CITY TX 57702-5225
105 W CHERRY POINT DR DALLAS TX 75232-3626
150 TUBBS LN LONOKE CA 72086-8490
14538 ARLINGTON TER JAMAICA NY 11435-5418

I would like to have output:
State Count
CA 2
TX 2
NY 1

X002548
Not Just a Number

15586 Posts

Posted - 2007-05-09 : 11:30:48

Select Count(Name) as [Count], STATE
From Table
Group by STATE

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-09 : 11:32:10

[code]Select State, Count(State) as [Count]
From Table
Group by State
Order by 1[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

battle59
Starting Member

8 Posts

Posted - 2007-05-09 : 11:45:57

It worked when I used the following:
SELECT `STATE`, COUNT(`STATE`) AS COUNT
FROM `CK`
GROUP BY `STATE`
ORDER BY 1

Just one additional question, is there a reason why the query was limited to 30 records?
ex., the query shown as used was:
SELECT `STATE` , COUNT( `STATE` ) AS COUNT
FROM `CK`
GROUP BY `STATE`
ORDER BY 1
LIMIT 0 , 30

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-09 : 12:04:40

This site is not for posting MySQL questions and certainly it is not allowed to post your questions without specifying the technology you are using !

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-09 : 12:09:43

Is it me or are we seeing more and more of these lately? At least one or two a day, either MySQL or Oracle ....

- Jeff
http://weblogs.sqlteam.com/JeffS

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-09 : 12:18:59

Nor do people care to post questions in appropriate forums. I daily see at least one wrong post in Script Library forum.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

battle59
Starting Member

8 Posts

Posted - 2007-05-09 : 14:13:26

This was an oversight on my part and I do apologize. I wasn't aware of where the query would post as I simply clicked new topic. I appreciate the help and am not exactly sure what I am using other than it is SQL databases that I use through php Myadmin. I am learning more about everything as I go along. I appreciate the help that the sqlteam forums offer and would never intentionally do that.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-09 : 15:04:43

No, you misunderstand: This website is for Microsoft SQL Server. Not MySQL. They are two completely different products. It is like asking for help regarding for your Mac at a Windows messageboard. Apples and oranges. Does that make sense?

- Jeff
http://weblogs.sqlteam.com/JeffS

battle59
Starting Member

8 Posts

Posted - 2007-05-09 : 15:32:57

Yes, that makes sense as I thought that all SQL was pretty much the same. So the entire sqlteam site is for Microsoft SQL only?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-09 : 15:37:18

Yes This site is only for the MS SQL SERVER , you can try out dbforums or mysql.com ..

Chirag

http://chirikworld.blogspot.com/

battle59
Starting Member

8 Posts

Posted - 2007-05-09 : 15:40:18

Ok. Thank you. :)