I am trying to calculate or count number of rows that have a date diff of a certain amount but am not sure how

select count(case when date_diff('day', min_time, max_time) <= 7 then 1 else null end)  / count(*)
from table 

but it doesn't seem to do the trick, not sure if there is a better way to implement this, thanks!

CodePudding user response：

Edit: as @shawnt00 mentioned in a comment on the question, the problem is most likely the fact that you're dividing integers (resulting in an integer too, ie 0 or 1 in this case). Adding a * 1.0 should fix the problem (or a cast to float8 if you prefer).

You can use the FILTER clause for this:

select count(*) filter (where date_diff('day', min_time, max_time) <= 7) * 1.0 / count(*)
from table 

Though since it looks like you're computing an average maybe something like this would be the easiest:

select avg(case when date_diff('day', min_time, max_time) <= 7 then 1.0 else 0.0 end)
from table 

CodePudding user response：

Why not use a count(*) with a where clause to select the rows?