CODEWITHSUNDEEP
sqldatabasepostgresql
ryan
ryan

Reputation: 99

Query that gets information from multiple tables

I am writing a bunch of queries that are getting information from multiple tables or actually uses sub queries to initialize them. The query that I am having trouble with right now actually is supposed to make another field thats not in either of the tables. The exact definition of the query that I am writing is as follows...

List the country name, it’s population, and the sum of the populations of all cities in that country. Add a fourth field to your query that calculates the percent of urban population for each country. (For the purposes of this example, assume that the sum of the populations of all cities listed for a country represent that country’s entire urban population.) Order the results of this query in increasing order of urban population percentage.

The code that I have tried for this query is as follows

SELECT name, population, SUM(population) FROM what.country JOIN what.city ON city.country_code = 
country.country_code GROUP BY population ASC

The tables that I am using are

               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

               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: 1

Views: 86

Answers (1)

Mureinik
Mureinik

Reputation: 312289

You have most of the problem solved - the only thing missing is the percentage of urban population which you can achieve by dividing the sum of the urban population (which you've already calculated) by the total population:

SELECT   cnt.name AS country_name, 
         cnt.population AS total_population, 
         SUM(cty.population)/(cnt.population) * 100 AS urban_percentage
FROM     what.country cnt 
JOIN     what.city cty ON cty.country_code = cnt.country_code 
GROUP BY cnt.name 
ORDER BY 3 ASC

Upvotes: 1

Related Questions