CODEWITHSUNDEEP
sqlpostgresqlaggregate-functions
arilwan
arilwan

Reputation: 3993

Logic to use in place of Aggregate function the WHERE clause

I am computing the frequency of values over a large table. since the result of the aggregate functions result in zero for some rows, I would like to simply skip such rows. I could have used NULLIF(..) but I do not want to return a zero value. And aggregate functions aren't allowed in the WHERE clause. How do I refactor the my query to skip such rows?.

SELECT device_id,
       COUNT(*) / (MAX(signal_strength) - MIN(signal_strength)) as freq
FROMm dcu
WHERE MAX(signal_strength) - MIN(signal_strength) !=0
GROUP BY device_id

ERROR: aggregate functions are not allowed in WHERE
LINE 4: where max(signal_strength) - min(signal_strength) !=0

Upvotes: 0

Views: 56

Answers (2)

The Impaler
The Impaler

Reputation: 48850

You must use HAVING instead of WHERE for aggregate computations. For example:

SELECT device_id,
       COUNT(*) / (MAX(signal_strength) - MIN(signal_strength)) as freq
FROMm dcu
GROUP BY device_id
HAVING MAX(signal_strength) <> MIN(signal_strength)

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

With the aggregate function, you cannot use where clause - you need to use having clause

SELECT device_id,
       COUNT(*) / (MAX(signal_strength) - MIN(signal_strength)) as freq
FROMm dcu
GROUP BY device_id
having MAX(signal_strength) - MIN(signal_strength) !=0

Upvotes: 1

Related Questions