On Mon, Aug 18, 2014 at 10:52 AM, John McKown
<john.archie.mckown@xxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
