SQL; Functions and where clause

Question

I'm trying to figure out which country has the population that is closest to the global average country population.

I have the following relation;

   Column   |         Type          | Modifiers 
------------+-----------------------+-----------
 name       | character varying(35) | not null
 code       | character varying(4)  | not null
 capital    | character varying(35) | 
 province   | character varying(35) | 
 area       | numeric               | 
 population | numeric               |

I tried doing this, where I simply select the name, the population for the country, getting the average country population and just compare it with a where clause. Finally I would just put a limit 1 to get the desired result. Oddly enough, I couldn't do this, what am I doing wrong? I'm using PostgreSQL.

select name, population, avg(population) as gac 
from country 
where population <= avg(population);

The error; ERROR: aggregates not allowed in WHERE clause LINE 1: ...pulation) as gac from country where population <= avg(popula...

juergen d · Answer 1 · 2014-02-13T18:53:27.187

2

You can order by the minimal difference between population of one country and the total average

select name, population, population, 
       (select avg(population) from country) as gac
from country 
order by abs(population - (select avg(population) from country))

The last line does the following:

get the average population from the country table
```
select avg(population) from country
```
substract the average population from the population of the current country (current row)
```
population - (select avg ...)
```
get the absolute value of that calculation - reverse negative values to positive ones
```
abs(...)
```
order by that calculation. It starts from the population with the lowest difference to the average population

edited Feb 13 '14 at 18:53

answered Feb 13 '14 at 10:20

juergen d

201,996
37
293
362

Thank you for the help! It seems to work. But what does the last line actually do? I can't get a grip on it. – Stabbah Feb 13 '14 at 11:17
Great! Thank you once again. – Stabbah Feb 15 '14 at 12:24

score 1 · Accepted Answer · answered Feb 13 '14 at 11:52

1

I would probably use window function:

select
    name, population, population
from 
    country 
order by
    abs(population - avg(population) over())
limit 1

Or cross-joined countries with global average:

select
    name, population, population
from 
    country 
    cross join (select avg(population) gac from country) gac
order by
    abs(population - gac.gac)
limit 1

answered Feb 13 '14 at 11:52

Tomas Greif

21,685
23
106
155

The first one + the use of the "over()" function I didn't know about was clean as f**k! Great, I've learned a lot, thank you for your help! Programmers unite! :) – Stabbah Feb 13 '14 at 13:38

SQL; Functions and where clause

2 Answers2