CODEWITHSUNDEEP
sqlpostgresql
ryan
ryan

Reputation: 99

Creating a query that gets data from multiple tables

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

Upvotes: 0

Views: 96

Answers (2)

Ram
Ram

Reputation: 3091

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

Upvotes: 0

bigonez
bigonez

Reputation: 111

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

Upvotes: 1

Related Questions