Home > mailing lists

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

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

From

Dorian Hoxha

Date:

18 August 2014, 17:28:49

I have CREATE TABLE t (country text, city text);

I want to get with 1 query,

select count(country),country GROUP BY country ORDER BY count(country) DESC

And for each country, to get the same for cities.

Is it possible ?

Thanks

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

From

John McKown

Date:

18 August 2014, 18:52:30

On Mon, Aug 18, 2014 at 9:28 AM, Dorian Hoxha <dorian.hoxha@gmail.com> wrote:

I have CREATE TABLE t (country text, city text);

I want to get with 1 query,

select count(country),country GROUP BY country ORDER BY count(country) DESC

And for each country, to get the same for cities.

Is it possible ?

Thanks

Just to be sure that I understand what you want. Will you have a given country, city combination occur multiple times? And, if so, you want to know how many times such a combination occurs (count city by country)? So you want a result which has one row for each country, city combination which lists the city name, country name, count(city within country), count(total times country occurs,regardless of city). It also depends on what you mean by "one query". I would hope you mean "sending a single SELECT command to the database server and getting the result set'. I ask because you could possibly do something like:

SELECT 'city',count(city),city,country FROM t GROUP BY city, country ORDER BY 2 DESC

UNION

SELECT 'country',count(country), country, NULL FROM t GROUP BY country ORDER BY 2 DESC

But I'm thinking that's not what you want.

Note: ORDER BY 2 means to order by the second output column, which is just a shorter way of saying "count(....)". I'm lazy.

===

Now, assuming that what I think you want really is what you want, I have some "hairy" SQL for you to try. The SQL query below seemed to work for me on some test data that I just "faked up" on my own.

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;

You may well wonder about that "avg(b.countrycount)::int" in the top line. That is _funky_, I will admit. Why does it work? Because I group by b.country. And for each given value of b.country, b.countrycount is the same value (think about it). So if you add up "n" copies of a specific integer number, then divide by "n", you get back the same number. This is to satisfy the SQL requirement that the column either be in the GROUP BY (which I don't want) or be in an aggregate funciton. So I picked avg() as an aggregate function that "just happens" (by design) to have the original value that I need. I'm sneaky! The ::int at the end casts the result from a floating point number back to an integer. This, to me, is a nicer display and is, again, guaranteed to be correct due to the way things are being calculated. Sorry if I'm being a bit "pushy" on this point. This point is what could be the most confusing to someone else, so I want to try to explain my thought process as well as I can.

You can rearrange the columns in the SELECT, but if you do be sure to change the values in the ORDER BY to still be the same column values. Again, I'm too lazy to type in the column names.

I am not saying the above is the best possible SQL. But it did work for me in my testing.

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

Genghis Khan

Maranatha! <><
John McKown

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

From

John McKown

Date:

28 August 2014, 15:35:49

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

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

From

Dorian Hoxha

Date:

28 August 2014, 16:27:02

Thanks John.

On Thu, Aug 28, 2014 at 2:35 PM, John McKown <john.archie.mckown@gmail.com> wrote:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general