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

[John McKown <john.archie.mckown@xxxxxxxxx>]

On Mon, Aug 18, 2014 at 9:28 AM, Dorian Hoxha <dorian.hoxha@xxxxxxxxx> 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