# Calculate average cumulative aggregate

**URL:** https://community.cratedb.com/t/calculate-average-cumulative-aggregate/466
**Category:** SQL
**Created:** [July 28, 2020, 2:31pm UTC](https://community.cratedb.com/t/calculate-average-cumulative-aggregate/466 "2020-07-28T14:31:50Z")
**Posts on this page:** 2
**Page:** 1

<div class="post-metadata">

### Author: ![Stefan\_Wolf](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/stefan_wolf/32/190_2.png) [@Stefan\_Wolf](https://community.cratedb.com/u/Stefan_Wolf)
#### Post date: [July 28, 2020, 2:31pm UTC](https://community.cratedb.com/t/calculate-average-cumulative-aggregate/466/1 "2020-07-28T14:31:50Z")

</div>

Hello to all,

I try to cumulative an average.  
this is the query for average

`select date_trunc('hour', timestamp) as time, avg(value) FROM logs where timestamp BETWEEN 1595844260934 AND 1595930660934 AND sensorid = 5 group by time order by time`

now I try to cumulative the average

`select date_trunc('hour', timestamp) as time, SUM(avg(value)) over (order by 'time') FROM logs where timestamp BETWEEN 1595844260934 AND 1595930660934 AND sensorid = 5 group by time order by time`

but I get the sum in every row and not cumulated

[here](https://www.it-swarm.dev/de/sql/kumulative-summe-postgresql-zaehlen/971331864/) is an example where they sum a aggregate count function with postgres

maybe someone can point me the right direction

---

<div class="post-metadata">

### Author: ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)
#### Post date: [July 29, 2020, 12:23pm UTC](https://community.cratedb.com/t/calculate-average-cumulative-aggregate/466/2 "2020-07-29T12:23:59Z")

</div>

> but I get the sum in every row and not cumulated

this is expected, as the window you defined, is over all rows.

```sql
order by 'time'

```

is no correct reference to the time column. This probably should throw an exception though.