Thanks John.
On Thu, Aug 28, 2014 at 2:35 PM, John McKown <john.archie.mck...@gmail.com> wrote: > On Mon, Aug 18, 2014 at 10:52 AM, John McKown > <john.archie.mck...@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 > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
