Want to calculate avg value per user

Question

I have data as follows

ID           Difference in days between 2 consecutive orders
abc.         2
abc.         3
abc.         7
xyz.         4
xyz.         5

I want the result as

ID .      Avg time difference between 2 orders
abc.      3(=12/4)
xyz.      4.5(=(4+5)/2

Possible duplicate of [SQL. Average entries per month](https://stackoverflow.com/questions/8271130/sql-average-entries-per-month) — WolfieeifloW, Mar 21 '19 at 20:04
I'm exactly doing the same thing as here https://stackoverflow.com/questions/9994862/date-difference-between-consecutive-rows However I want the result to show me **avg of difference in days** per every account_number and **not** one row for every difference in days. When I add avg(datediff(d1,d2)), i'm getting following error: [Code: -112, SQL State: 42607] The operand of the column function "AVG" includes a column function, a scalar fullselect, or a subquery. https://i.stack.imgur.com/MY14O.png — dj17, Mar 21 '19 at 21:06

score 0 · Answer 1 · answered Mar 21 '19 at 19:59

0

you could use AVG and group by

select  id, avg(Difference ) 
from my_table  
group by id

answered Mar 21 '19 at 19:59

ScaisEdge

avg(difference) is throwing an error. I'm doing avg(timestampdiff(8, date1-date2)) – dj17 Mar 21 '19 at 20:08
you have error ? .. show error message .. wrong result .. show a coherent data sample you actual result and the expected result .. – ScaisEdge Mar 21 '19 at 20:11
I'm trying to do something exctly as this https://stackoverflow.com/questions/55288363/want-to-calculate-avg-value-per-user/55288400?noredirect=1#comment97306453_55288400 However I want the avg and not individual rows as in the above case. When I add avg(timestampdiff) I'm getting the following error: 1) [Code: -112, SQL State: 42607] The operand of the column function "AVG" includes a column function, a scalar fullselect, or a subquery.. SQLCODE=-112, SQLSTATE=42607, DRIVER=4.22.29 – dj17 Mar 21 '19 at 20:37

1 Answers1