CODEWITHSUNDEEP
sqlpostgresql
Now.Zero
Now.Zero

Reputation: 1389

Writing SQL query : getting the average value per minute

I want to make a query getting the average created value per minute. I don't come up with any idea but using subquery like below. Would there be any other ways using only one query (without subquery)

with item_cnt_per_min as (
    select date_trunc('minute', created), count(*) as cnt
    from item
    where created > '2020-09-21 06:10'
    group by 1
    order by cnt desc
)
select avg(cnt) from item_cnt_per_min

Upvotes: 0

Views: 839

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

The average created value could be calculated as:

select count(*) / count(distinct date_trunc('minute', created))
from item 
where created > '2020-09-21 06:10';

That is, divide the total by the number of minutes.

Upvotes: 1

Related Questions