Originally Posted by
June7
Quote marks in field names is bad idea. Advise no spaces, special characters, punctuation (underscore is exception) in names nor reserved words as names. Better would be A_CPU, B_CPU, C_CPU.
Data is not normalized, multiple similar field names is an indicator, and that may be what is making this difficult. Why are you saving calculated values (Total) into table?
The aggregate calcs would work if 0s where nulls. Following gets your desired values (note changed field names):
SELECT Sum(IIf([A_CPU]=0,Null,[A_Total]))/Sum(IIf([A_CPU]=0,Null,[A_Volume])) AS [A wgt avg CPU], Sum(IIf([B_CPU]=0,Null,[B_Total]))/Sum(IIf([B_CPU]=0,Null,[B_Volume])) AS [B wgt avg CPU], Sum(IIf([C_CPU]=0,Null,[C_Total]))/Sum(IIf([C_CPU]=0,Null,[C_Volume])) AS [C wgt avg CPU]
FROM Data;