mardi 30 juin 2015

Select Row with MAX value while using another GROUP BY

I'm brand new to learning SQL, so my apologies in advance is my question is elementary.

What I'm looking to do is display a column that would tell me the country with the highest and lowest values, respectively, of percent_aged_over_60 within already distinguished groups of "old_country" and "young_country":

SELECT
    CASE
        WHEN percent_aged_over_60 >= 10 THEN "old_country"
        WHEN percent_aged_over_60 < 10 THEN "young_country"
        ELSE "error"
    END AS "oldness",
COUNT(*)
FROM countries_by_population
    GROUP BY oldness;

In other words, I'm looking to add a column that will tell me the name of the country with MAX(percent_aged_over_60) for "old_country" and the MIN(percent_aged_over_60) for "young_country".

While I'm sure this is easiest to do as two separate queries, I would like to do this within the same query.

As such, I'm looking for a result that produces three columns - oldness, count(*) and country.

Please let me know if I can clarify anything!

Thank you for your help!

Aucun commentaire:

Enregistrer un commentaire