Average IF statement-syntax error (missing operator) in a query expression (1 Viewer)

hello guys, i am trying to get Average If function to access sql
i have columns Period and Costs_Per_Capita, result should be like like this:

Costs_Per_Capita Period CALCULATED_Period_Avg_Costs
15,505 1 15976.27582
16,368 1 15976.27582
16,037 1 15976.27582
15,995 1 15976.27582
15,000 2 16000
17,000 2 16000

i tried:
SELECT Costs.Costs_Per_Capita, Costs.Period
IIF (Period = 1, (Select AVG(Costs_Per_Capita) From Costs Where Period = 1),
(Select AVG(Costs_Per_Capita) From Costs Where Period = 2)
AS result
FROM Costs;

but get "syntax error (missing operator) in a query expression ..."
any advice where i am doing mistake?:banghead:

Why don't you simply use,

pr2-eugin said:

Why don't you simply use,

Code:Copy to clipboard

SELECT Costs.Costs_Per_Capita, Costs.Period, Avg(Costs.Costs_Per_Capita) As CALCULATED_Period_Avg_Costs
FROM Costs
GROUP BY Costs.Costs_Per_Capita, Costs.Period;

Click to expand...

i need get average of Costs_Per_Capita from values where Period is 1 to that row where period is 1.
Excel equivalent is
=SUMIF([Period],"="&[@[Period]],[Costs_Per_Capita])/COUNTIF([Period],"="&[@[Period]])
see the table what i want, but thanks for reply

Oh okay ! Sorry did not read your question properly, try this.

thank you! that works perfect!
but could you explain me what is behind that code?or what is wrong with my suggestion of code? i am newbie so i am trying to understand it..

You were missing a comma after the Costs.Period. Then you missed a closing Parens for the IIF. The restructured one would be.
The problem with this is, if you need another period, you are going to complicate the IIF. So to avoid, I created a Sub Query that grouped by the Period and performed an average on that. Then JOINED this subquery to the main query.

The subQuery will result in two rows like,
Then the data we need is to JOIN them to get the actual data.

wow many thanks!
firstly i did query with averages for periods as your subquery is, but then i wasnt able to continue...i just didnt know how to match these averages to the main query..thanks again!

Not a problem ! Glad to help. Good luck

pr2-eugin said:

Not a problem ! Glad to help. Good luck

Click to expand...

Hi again, one more question here. I need to modify my project. I have the same situation as described above but there is two more citeria, i need average not just by period but also by office and KPI_ID
so i have>
KPI_ID OFFICE_ID PERIOD_ID Calc_KPI_Weighted_Value
and i need average of Calc_KPI_Weighted_Value for the same combination of first three columns. It means:
KPI_ID OFFICE_ID PERIOD_ID Calc_KPI_Weighted_Value [wanted value]
1 1 1 10 20
1 1 1 20 20
1 1 1 30 20
2 1 1 10 15
2 1 1 20 15
3 1 1 20 20
3 2 1 20 25
3 2 1 30 25
3 2 2 40 40
what i tried till now is the same as previous exercise plus OFFICE_ID.
SELECT KPI_ID, OFFICE_ID, PERIOD_ID, tmpQry.CALCULATED_Relative
FROM qry_KPI_Values_Norm_And_Weighted_Calc
INNER JOIN (SELECT qry_KPI_Values_Norm_And_Weighted_Calc.PERIOD_ID, qry_KPI_Values_Norm_And_Weighted_Calc.OFFICE_ID
Avg(qry_KPI_Values_Norm_And_Weighted_Calc.Calc_KPI_Weighted_Value) AS CALCULATED_Relative FROM qry_KPI_Values_Norm_And_Weighted_Calc
GROUP BY qry_KPI_Values_Norm_And_Weighted_Calc.PERIOD_ID, qry_KPI_Values_Norm_And_Weighted_Calc.OFFICE_ID)
AS tmpQry ON [qry_KPI_Values_Norm_And_Weighted_Calc].PERIOD_ID, qry_KPI_Values_Norm_And_Weighted_Calc.OFFICE_ID = tmpQry.PERIOD_ID;