SQL mean amount per group ordered by time

user_12052180 0
Viewed 37

I have the following table

timestamp   ID      eur 
----------------------- 
2022-01-01  A       10
2022-01-02  A       20
2022-01-01  B       30
2022-01-02  B       40
2022-01-03  B       50
2022-01-04  B       60

Now I am interested in all previous information for a specific ID. Then I want to do something with this information, lets say calculating the mean. Here is what I am aiming for:

timestamp   ID      eur     sum_all     mean_all    
------------------------------------------------    
2022-01-01  A       10      10          10
2022-01-02  A       20      30          15
2022-01-01  B       30      30          30
2022-01-02  B       40      70          35
2022-01-03  B       50      120         40
2022-01-04  B       60      180         45

This seems so easy but I just can't get my head around how to do this in SQL.

I appreciate any help. Thanks!

concatenation sum postgresql average sql
1 Answers

You can use the sum and avg window functions:

select *, sum(eur) over(partition by ID order by timestamp) as sum_all, 
avg(eur) over(partition by ID order by timestamp) as mean_all
from table_name
user_176450990
Related