I have a lookup table that looks like this (:
Column 1 | Column 2 | Column 3 | Column 4 |
Value 1 | - | - | 15 |
Value 1 | - | - | 60 |
Value 2 | - | - | 75 |
Value 2 | - | - | N/A |
Value 2 | - | - | 5 |
I want to calculate the average for all of the values in Column 4 (that aren't N/A) that have the same value in Column 1. Then I want to output that as a table:
Column 1 | Column 2 |
Value 1 | 37.5 |
Value 2 | 40 |
The stats command can do that, although I'm not sure how it will handle "N/A".
| stats avg('Column 4') as "Column 2" by 'Column 1'