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
