D.Wells D.Wells - 8 days ago 9
SQL Question

Getting the count of rows with the same

Trying to learn some sql lately and have come across this problem.
I have a page that displays a country name and its capital in divs, requested from my db. However I want to add a feature where the user can order the display based on the number of cities that a given country contains with a population greater than 1 million. I can't figure out the query that would be needed here however.

How would I obtain as an integer value the number of Cities within the same country with a population greater than 100? My table format is as such. So for example, England should return 2 as there are two english cities that fulfill the criteria, USA 3, India 1 etc. etc. So USA would appear top of my newly ordered results page, followed by England then India.

I've tried union queries but I cant get hold of the value of the first query.

How can I do this?

Table A

*-----------------*------------*
| COUNTRY NAME | CAPITAL |
| England | London |
| India | New Delhi |
| US | DC |
| | |
*-----------------*------------*


Table B

*-----------------*------------------------------*
| COUNTRY NAME |Cities with a population > 1m |
| England | London |
| England | Birmingham |
| India | New Delhi |
| US | DC |
| US | New York |
| US | LA |
*-----------------*-------------------------------*


Thanks for your help!

Answer

I'm assuming that all values in table B are cities over the population required. If not add a where clause to restrict the results..

SELECT [COUNTRY NAME], COUNT([COUNTRY NAME]) 
FROM Table B
GROUP BY [COUNTRY NAME]