jimmy
jimmy

Reputation: 11

Issue with writing a query from a table

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

Answers (1)

Ram
Ram

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

Related Questions