Обсуждение: Problems with group by ... order by
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
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. _______________________________
"John D. Burger" <john@mitre.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