Re: select top_countries and for each country, select top_cities in that country, in 1 query

On Mon, Aug 18, 2014 at 10:52 AM, John McKown
<john.archie.mckown@gmail.com> wrote:
>
> SELECT avg(b.countcountry)::int as "CountryCount", b.country, a.city,
> count(a.city) as "CityCount"
> FROM t AS a
> INNER JOIN
> (SELECT COUNT(country) AS countcountry, country FROM t GROUP BY country) AS
> b
> ON a.country = b.country
> GROUP BY b.country, a.city
> ORDER BY 1 DESC,4 DESC;
>

I am curious that nobody pointed out that the above might work but is
really poor code. Given that, I wonder what the people here think of
the following code. It seems "better" to me, even if it is more wordy.

WITH CountryCount AS (
     SELECT COUNT(country) as "countryCount",
            country
            FROM t
            GROUP BY country
    ),
    CityCount AS (
    SELECT COUNT(city) as "cityCount",
           city,
           country
           FROM t
           GROUP BY country, city
    )
SELECT b."countryCount",
         b.country,
         a.city,
         a."cityCount"
FROM CityCount as a
INNER JOIN
    CountryCount AS b
    ON a.country = b.country
    ORDER BY b.countcountry DESC,
            a.city DESC


--
There is nothing more pleasant than traveling and meeting new people!
Genghis Khan

Maranatha! <><
John McKown