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