Skip to content
Advertisement

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

Advertisement

Answer

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.

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement