Problems with group by ... order by

Поиск
Список
Период
Сортировка
От John D. Burger
Тема Problems with group by ... order by
Дата
Msg-id 1307c6f5709cab391e87d78e6744f605@mitre.org
обсуждение исходный текст
Ответы Re: Problems with group by ... order by  (Bricklen Anderson <BAnderson@PresiNET.com>)
Re: Problems with group by ... order by  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
I can't figure out why the following doesn't work:

   select
    (case
        when count1 < 300 then 'Other'
        else country1
        end) as country2,
    sum(count1) as count2
    from (select coalesce(country, 'None') as country1, count(*) as count1
            from userProfiles group by country1) as counts1
    group by country2
    order by (country2 = 'Other'), count2 desc

Basically, I want to get an aggregate count of users across countries
(including the "None" country), then I want to aggregate those
countries with counts less than 300 into an Other pseudo-country.  I
want it sorted by this final count, except I want the Other entry to be
last.  This works fine if I leave out the first order-by condition -
the result is sorted by the final count.  But with the query as written
above, I get:

   ERROR:  column "country2" does not exist

It also works fine if I just order by country2, count2 - it seems to be
the comparison that's the problem.  And ordering by a boolean after
aggregating works fine in this simpler case:

   select country, count(*) as cnt
    from userProfiles
    group by country
    order by country is null, cnt;

This puts the NULL count at the end.

This is with PG 7.4.7.  Any advice appreciated, including how to do
this in a simpler fashion.

- John Burger
   MITRE


В списке pgsql-general по дате отправления:

Предыдущее
От: "Cristian Prieto"
Дата:
Сообщение: Text/Varchar performance...
Следующее
От: Josh Berkus
Дата:
Сообщение: Re: [PERFORM] Text/Varchar performance...