CODEWITHSUNDEEP
sqlpostgresqlgroup-bymin
Andrei Ivanov
Andrei Ivanov

Reputation: 311

Min function in postgresql

I am trying to find a division with the lowest population density to do so i did the following:

SELECT P.edname, MIN((P.total_area*1000)/P.total2011) AS "Lowest population density"
FROM eds_census2011 P
GROUP BY P.edname
HAVING COUNT (*)> 1

total_area is multiplied by 1000 (so it is in square metres) and divide by total population. I want only one record displaying the division (edname) and the population density wich is calculated (MIN((P.total_area*1000)/P.total2011)), instead I get all the records - not even sorted...

The problem is that I have to group it by edname, if I leave out the GROUP BY and HAVING lines I get an error. Any help is greatly appriciated!

Upvotes: 0

Views: 204

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656391

Without subquery:

SELECT p.edname, min((p.total_area * 1000)/p.total2011) AS lowest_pop
FROM   eds_census2011 p
GROUP  BY p.edname
HAVING COUNT (*) > 1
ORDER  BY 2
LIMIT  1;

This one returns only 1 row (if any qualify), even if multiple rows have equally low density.

If you just want the lowest density, period, this can be much simpler:

SELECT edname, (total_area * 1000)/total2011) AS lowest_pop
FROM   eds_census2011
ORDER  BY 2
LIMIT  1;

Upvotes: 1

peterm
peterm

Reputation: 92785

Try

SELECT edname, (total_area*1000/total2011) density
  FROM eds_census2011
 WHERE (total_area*1000/total2011) = (SELECT MIN(total_area*1000/total2011) FROM eds_census2011)

SQLFiddle

A 'Return only one row' rule could be easily enforced by using LIMIT 1 if it's really necessary

Upvotes: 1

Related Questions