query on states & cities

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

i have two tables: states and cities

states
----------
 - stateid
 - statename

cities
--------
 - cityid
 - cityname
 - stateid

i want display statename with no of cities in that state

statename    count(cities)
---------    -----
keral          5
chennai        10

Can any one tell me the sql query to get the result?

Posted 25-May-12 0:27am

Srinivas Kumar Pasumarthi

Updated 25-May-12 0:30am

Maciej Los

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2012-05-25T00:33:00

Solution 1

Like this:

SQL

SELECT s.statename, COUNT(c.cityid) AS CountOfCities
FROM cities as c LEFT JOIN states as s ON c.stateid = s.stateid
GROUP BY s.statename

As Manfred R. Bihy wrote in comment, if you would like to get all states and cities, even if state has no city - probably it's impossible ;) - you need to use query like this:

SQL

SELECT s.statename, COUNT(c.cityid) AS CountOfCities
FROM states as s LEFT JOIN cities as c ON s.stateid = c.stateid
GROUP BY s.statename

Thank you Manfred ;)

Posted 25-May-12 0:33am

Maciej Los

Updated 25-May-12 1:07am

v3

Comments

Manfred Rudolf Bihy 25-May-12 6:44am

I'm not sure if this is a requirement, but if there is a state that does not have any cities associated with it, it will not show up with a city count of zero. That is why I chose to join the tables the other way. Anyhow, take a 5. OP did not state any specific requirement.

:)

Maciej Los 25-May-12 6:46am

I agree with you ;)
Thank you :)

Manfred Rudolf Bihy 25-May-12 7:42am

You're welcome. Actually before I had seen your solution I had used an inner join and your left join got me thinking of the consequences. So you did indeed help me too. :)
Thanks for that!

Srinivas Kumar Pasumarthi 25-May-12 6:57am

its working properly meets my requirement

Maciej Los 25-May-12 7:08am

OK, thank you for the information ;)

Manfred Rudolf Bihy 25-May-12 7:47am

I wouldn't say "impossible". Just think of a scenario where the selected cities are constrained by a where clause. For example when counting the number of cities for all states that have a population of over n citizens. If n is chosen big enough there might be some states that will come out with a count of zero. So there is indeed a basic importance of how the join is being made.

Manfred Rudolf Bihy · Accepted Answer · 2012-05-25T00:33:00

Solution 2

Please try the following SQL statement:

SQL

SELECT st.StateName, COUNT(cy.cityid) as NoOfCities
FROM states st LEFT JOIN cities cy ON st.StateId == cy.StateId
GROUP BY st.StateName

Regards,

Manfred

Posted 25-May-12 0:33am

Manfred Rudolf Bihy

Updated 25-May-12 0:41am

v5

Comments

Maciej Los 25-May-12 6:35am

Good one, +5!

Manfred Rudolf Bihy 25-May-12 6:41am

Thanks! :)

VJ Reddy 25-May-12 6:38am

Good answer. 5!

Manfred Rudolf Bihy 25-May-12 6:41am

Thanks VJ! :)