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!
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]