Simple Problem - Brain Freeze

[Paul Smith]

My data:

F1 F2 F3
a 0 20
a 4 40
a 0 40
b 1 15
b 0 50

I want to find the average for each value in F1 where there is a value in
column 1. Finding the sum of each required F2 is no problem, but when using
the Group By clause I cannot seem to eliminate the surplus F3s.

I want:

a 10
b 15

I get

a 25
b 65

 

[Allen Browne]

I think this is what you are after:
SELECT F1, Sum([F3]) / Sum([F2]) As AverageOfF3
FROM MyTable
GROUP BY F1
HAVING Sum([F2]) <> 0;

Or possibly you wanted:
SELECT F1, Sum([F3]) / Sum([F2]) As AverageOfF3
FROM MyTable
WHERE F2 <> 0
GROUP BY F1;

This assumes there are no nulls in any field to consider.