I get an error when I try and run my query and I can't figure out why. This is the last step of my homework, and ive been trying to figure it out for a while now. The query that I am trying to write is.
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 error that I am receiving right now is
Warning: pg_query(): Query failed: ERROR: column "cnt.population" must appear in the
GROUP BY clause or be used in an aggregate function LINE 2
The code that I have for this is
$query ="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";
The two 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
life_expectancy | real |
gnp | real |
gnp_old | real |
local_name | character varying(45) | not null default ''::character varying
government_form | character varying(45) | not null default ''::character varying
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
You can use MAX for city population as you are doing GROUP BY on city name, there will be only one value for population for that city
$query ="SELECT cnt.name AS country_name,
Max(cnt.population) AS total_population,
SUM(cty.population), SUM(cty.population)/max(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";