CODEWITHSUNDEEP
sqlpostgresql
Marrento
Marrento

Reputation: 329

Calculating aggregated field

I'm back with more potgresql question :)

I have a query that shows the amount of time between when a plan was created and when a plan was updated.

select
  ...

  EXTRACT(DAY FROM MAX(plans.updated_at) - MIN(plans.created_at)) AS DateDifference

  ...

Now I would like to exclude the results when the number of days is 0

So I tried:

WHERE EXTRACT(DAY FROM MAX(plans.updated_at) - MIN(plans.created_at)) > 0

But I get Aggregated functions are not allowed in where.

What would be the best way to accomplish this?

Thanks in advance

Upvotes: 0

Views: 20

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

If your query does what you want, then you would use a having clause:

HAVING EXTRACT(DAY FROM MAX(plans.updated_at) - MIN(plans.created_at)) > 0

Conditions on aggregated values need to be handled after the aggregation. Hence, the HAVING clause goes after the GROUP BY.

Upvotes: 2

Related Questions