Given columns A and B from Table1:
A B Small 3 Med 4 Med 1 Large 2 Small 1
Given columns S, M, L from Table2:
S M L 5 5 3 8 2 1 4 6 5 2 2 8
I want to create a new column in Table1 that outputs the quotients of values of column B and Avg(Table2.S) only if the entry in the same row in column A of is ‘Small’. If column A has ‘Med’, we divide by Avg(Table2.M) and if it has ‘Large’, we divide by Avg(Table2.L). It would continue to check this row by row. For example, in the first row of column B, we have ‘3’, and on the same row in column A, we have ‘Small’, so we would output 0.6316 (3/Avg(Table2.S)). So the expected column, let’s call it C, would be:
C 0.6316 1.0667 0.2667 0.4076 0.2105
How would I output C using MS Access SQL? I tried
Select Switch(Table1.A ='Small', Table1.B/Avg(Table2.S), Table1.A ='Med', Table1.B/Avg(Table2.M), Table1.A ='Large', Table1.B/Avg(Table2.L)) From Table1, Table2
but I usually get an error that says my query doesn’t include the specified expression as an aggregate function.
Advertisement
Answer
Here is a way to do this
select A,B,switch(A="Small",x.avg_s,A="Med",avg_m,A="Large",avg_l) as avg_vals,B/switch(A="Small",x.avg_s,A="Med",avg_m,A="Large",avg_l) from table1 y, (select avg(S) as avg_s,avg(M) as avg_m,avg(L) as avg_l from table2 )x