Perform an operation on values in the column of a lookup table that share the same row values

[matthewob5]

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

[richgalloway]

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'