Lists: | pgsql-general |
---|
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 |
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
From: | Bricklen Anderson <BAnderson(at)PresiNET(dot)com> |
---|---|
To: | "John D(dot) Burger" <john(at)mitre(dot)org> |
Cc: | PostgreSQL general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Problems with group by ... order by |
Date: | 2005-10-05 19:05:47 |
Message-ID: | 4344240B.3050101@PresiNET.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
John D. Burger wrote:
> 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
>
Do either of these work for you? Note, completely untested, and just off the top
of my head.
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 (1 = 'Other'), count2 desc
select
(case
when count1 < 300 then null
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 (1 is null), count2 desc
--
_______________________________
This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
_______________________________
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "John D(dot) Burger" <john(at)mitre(dot)org> |
Cc: | PostgreSQL general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Problems with group by ... order by |
Date: | 2005-10-05 19:11:11 |
Message-ID: | 1632.1128539471@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
"John D. Burger" <john(at)mitre(dot)org> writes:
> 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
> ERROR: column "country2" does not exist
ORDER BY (and also GROUP BY) permit references to output column names
only when they are *unadorned*. You cannot use them in expressions.
This is a compromise between SQL92 and SQL99 rules ... it's a bit ugly.
regards, tom lane