Min function in postgresql

Question

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!

multiplied by 1000 (so it is in square metres) .. That's odd. What would your base unit be? Sqares of 31.623x31.623 meters? Hard to believe. — Erwin Brandstetter
– Erwin Brandstetter, Commented May 10, 2013 at 0:21

peterm · Accepted Answer · 2013-05-10 00:43:39Z

1

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

edited May 10, 2013 at 0:43

answered May 9, 2013 at 23:56

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Erwin Brandstetter Over a year ago

@AndreiIvanov: This query ignores the HAVING count(*) > 1 part and is not equivalent. Well, the question is pretty vague about the exact goal.

peterm Over a year ago

@ErwinBrandstetter OP's goal was to just find the smallest density

Erwin Brandstetter Over a year ago

If that's all he wants, your answer is correct, but it could be much simpler, and the Q is misleading.

Erwin Brandstetter · Accepted Answer · 2013-05-10 00:52:03Z

1

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;

edited May 10, 2013 at 0:52

answered May 10, 2013 at 0:30

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

2 Comments

Andrei Ivanov Over a year ago

Thank for an answer, but this does not return the smallest density... Maybe because it is still grouped by edname!?

Erwin Brandstetter Over a year ago

@AndreiIvanov: I am pretty sure it does. To be precise: the smallest density among those where at least 2 rows for one edname exist. That's what HAVING COUNT (*) > 1 (like in your original query) does.

Collectives™ on Stack Overflow

Min function in postgresql

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related