I am trying to get overall average
number like this in each row. Can you help me how I can best achieve this using Snowflake SQL?
Overall Average
should be (300+150+100)/3
ID | Desc | SUB_ID | Value | Average by ID | Overall Average |
---|---|---|---|---|---|
1 | ABC | 10 | 300 | 300 | 183.33 |
1 | ABC | 20 | 150 | 300 | 183.33 |
1 | ABC | 30 | 450 | 300 | 183.33 |
2 | DEF | 10 | 150 | 150 | 183.33 |
2 | DEF | 20 | 150 | 150 | 183.33 |
3 | EFG | 30 | 100 | 100 | 183.33 |
3 | EFG | 10 | 180 | 100 | 183.33 |
3 | EFG | 20 | 20 | 100 | 183.33 |
I was able to get Average by ID
using window function AVG (Value) Over (partition by ID)
You need to sign in to view this answers
Leave feedback about this