Reputation: 11
I am trying to write a query for homework, but there is one query in particular that I am having trouble with. The one that I am having trouble with is supposed to get all forms of government from a table, and then get the count of how many countries have that form of government. I understand I would have to use the count function, but I don't understand how I would add 1 for each time government is mentioned. Also, the independent year thing is confusing me too. Here is the exact definition of the query
Generate a list of all forms of government with the count of how many countries have that form of government. Also, list the most recent year in which any country became independent with that form of government. The results should be ordered by decreasing count.
Here is the code that I tried, along with the table that I tried
SELECT government_form, COUNT(government_form) AS count, indep_year
FROM what.country
ORDER BY count DESC
Here is the table 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 |
gnp_old | real |
local_name | character varying(45) | not null default ''::character varying
government_form | character varying(45) | not null default ''::character varying
Upvotes: 1
Views: 46
Reputation: 3091
Generate a list of all forms of government with the count of how many countries have that form of government. Also, list the most recent year in which any country became independent with that form of government. The results should be ordered by decreasing count.
First lets understand the question in parts
count of how many countries have that form of government
means we need to GROUP BY
government_form
and then apply COUNT
on countries
most recent year in which any country became independent with that form of government
means we need to also find MAX
indep_year
after grouping by government_form
Thus the query is
SELECT government_form, COUNT(countries) AS count, MAX(indep_year)
FROM what.country
GROUP BY government_form
ORDER BY count DESC
Upvotes: 2