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(dot)archie(dot)mckown(at)gmail(dot)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