I have a initial table where I have the following rows: ID_0, geom and utilisatio (where the value varies from 1 to 7).

I am trying to create a table where I would have the count of a specific utilisatio value (associated to an ID_0) for every ID_0 that is within a range of 400m. So far I have come up with:

Select a.id_0, a.geom, count (b.utilisatio) as util_1
into new_table
from first_table as a 
    left join first_table as b ON ST_Dwithin (a.geom, b.geom, 400)
where a.id_0 <> b.id_0
group by a.id_0, a.geom

Now my problem is that I don't actually know where to add the following constraint to get only the specified utilisation code:

where utilisatio = 1

I am a beginner self-taught user of Postgres so I am sorry if this question is basic.

  • Seems like you want to use the HAVING clause – LowlyDBA - John McCall Sep 20 '16 at 16:29
  • HAVING is for when you need to filter on the RESULT of an aggregate .. it sounds like he wants to filter before the aggregate .. WHERE takes multiple conditions .. they just have to be separated by AND or OR .. WHERE a.id_0 <> b.id_0 AND b.utilisatio = 1 (or a.utilisatio?.. or both.. or a combination of the two..) – Joishi Bodio Sep 20 '16 at 16:31
  • @AlexGirard If you only want there to be a COUNT of 1, then yes. If you want the COUNT of utilisatio codes that are number 1, use Joishi's suggestion. – LowlyDBA - John McCall Sep 20 '16 at 16:34
  • Unrelated, but: select ... into new_table ... is discouraged. It's better to the standard compliant create table new_table as select ... – a_horse_with_no_name Sep 20 '16 at 16:58

As always, the documentation is an excellent reference. In here, it shows WHERE condition and condition is defined (further down the page) as

where condition is any expression that evaluates to a result of type boolean. Any row that does not satisfy this condition will be eliminated from the output. A row satisfies the condition if it returns true when the actual row values are substituted for any variable references.

HAVING, as John pointed out in a comment, is for filtering on aggregates after they have been calculated, while WHERE is for filtering the rows that are gathered at the start. For example:

  type, COUNT(type)
FROM table
HAVING COUNT(type) > 1

would return a set of rows where the COUNT(type) result ended up being more than 1.. The filtering done by HAVING takes place after the DB has already grouped the entire table by TYPE and calculated all COUNT() values. On the other hand

  type, COUNT(type)
FROM table

would return one row (where type is EXAMPLE) and the count of how many rows contained that type. The filtering here takes place AS THE DB COLLECTS THE ROWS up front .. Any row that doesn't have type 'EXAMPLE' is thrown out. Only one COUNT() is calculated in this instance.

One more complex example to show that the two are evaluated separately and can be combined in any query.

  col_1, col_2, col_3, SUM(col_4), COUNT(col_4), AVG(col_4)
FROM table
WHERE col_2 IN ('A', 'B', 'C')
  AND col_3 > 3
GROUP BY col_1, col_2, col_3
HAVING COUNT(col_4) > 2
  AND AVG(col_4) > 10.5
| improve this answer | |
  • It looks like WHERE is what I need. I am currently running the query and will update this thread when It's done. Thank you. – Alex Girard Sep 20 '16 at 16:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.