Creating a query that gets data from multiple tables

Question

I am trying to write queries for my class assignment, but I am having trouble with one query in particular. The query that I am having issues with counts all the cities in each country, and displays them from the largest number of cities to the smallest number of cities. The exact definition of the query that I am trying to write is...

List the countries in descending order beginning with the country with the largest number of cities in the database and ending with the country with the smallest number of cities in the database. Cities that have the same number of cities should be sorted alphabetically from A to Z.

I am going to now post the code that I have tried for this query along with the tables that I am using to complete it.

SELECT country.name 
FROM what.country as name 
INNER JOIN what.city as city ON name.country_code = city.country_code 
SORT BY name DESC

Here are the two tables that I am using.

             Table "what.country"
     Column      |         Type          |               Modifiers              
-----------------+-----------------------+--------------------------------------
 country_code    | character(3)          | not null default ''::bpchar
 name            | character varying(52) | not null default ''::character varying
 continent       | continent             | not null
 region          | character varying(26) | not null default ''::character varying
 surface_area    | real                  | not null default 0::real
 indep_year      | smallint              | 
 population      | integer               | not null default 0
 life_expectancy | real                  | 
 gnp             | real                  | 

             Table "what.city"
    Column    |         Type          |                     Modifiers                  
--------------+-----------------------+-----------------------------------------
 id           | integer               | not null default nextval('city_id_seq'::regclass)
 name         | character varying(35) | not null default ''::character varying
 country_code | character(3)          | not null default ''::bpchar
 district     | character varying(20) | not null default ''::character varying
 population   | integer               | not null default 0

bigonez · Accepted Answer · 2014-10-12 04:36:53Z

1

You can try to do a query as:

SELECT A.name AS name, IFNULL(B.cities, 0) AS cities
FROM what.country AS A
LEFT JOIN (SELECT country_code, count(id) AS cities FROM what.city GROUP BY country_code) AS B
ON A.country_code = B.country_code
ORDER BY cities DESC, name ASC

answered Oct 12, 2014 at 4:36

bigonez

1112 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ram · Accepted Answer · 2014-10-12 09:55:19Z

0

List the countries in descending order beginning with the country with the largest number of cities in the database and ending with the country with the smallest number of cities in the database. Cities that have the same number of cities should be sorted alphabetically from A to Z.

For finding the country with largest number of cities or smallest number of cities we need to use GROUP BY and COUNT where grouping by country and counting cities
For descending order use city_count DESC and for same number of cities should be sorted alphabetically use country_name

Code

SELECT country.name AS country_name, COUNT(city.id) AS city_count
FROM what.country as name 
INNER JOIN what.city as city ON name.country_code = city.country_code
GROUP BY  country.name
ORDER BY city_count DESC, country_name

edited Oct 12, 2014 at 9:55

answered Oct 12, 2014 at 4:37

Ram

3,09110 gold badges43 silver badges57 bronze badges

2 Comments

ryan Over a year ago

I appreciate the explanation it really helped me out!

vyegorov Over a year ago

Provided query will not work in PostgreSQL, as there's no SORT BY clause.

Collectives™ on Stack Overflow

Creating a query that gets data from multiple tables

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related