Problems with group by ... order by

From: "John D(dot) Burger" <john(at)mitre(dot)org>
To: PostgreSQL general <pgsql-general(at)postgresql(dot)org>
Subject: Problems with group by ... order by
Date: 2005-10-05 18:43:40
Message-ID: 1307c6f5709cab391e87d78e6744f605@mitre.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Josh Berkus 2005-10-05 19:00:48 Re: [PERFORM] Text/Varchar performance...
Previous Message Steinar H. Gunderson 2005-10-05 18:34:41 Re: Text/Varchar performance...