Select count question with where clause

Question

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.

• LowlyDBA - John M: Seems like you want to use the HAVING clause
• Joishi Bodio: 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..)
• LowlyDBA - John M: @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.
• user1822: Unrelated, but: select ... into new_table ... is discouraged. It's better to the standard compliant create table new_table as select ...

Answer 1

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:

SELECT
 type, COUNT(type)
FROM table
GROUP BY type
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

SELECT
 type, COUNT(type)
FROM table
WHERE type = 'EXAMPLE'
GROUP BY type

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.

SELECT
 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

• Alex Girard: 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.