How do I get Average IF statement in SQL?

Question

I have table in MS Access with columns Year, Period (values just 1 and 2), Costs_Per_Capita and CALCULATED_Period_Avg_Costs, PK is ID.

I need to calculate CALCULATED_Period_Avg_Costs. It should return average of Costs_Per_Capita for given period from Period column. In Excel I do this with SUMIF/COUNTIF which returns Average IF.

Do you have some advice how to write a code in SQL to do that?

Results should look like:

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 used statement:

SELECT 
Costs_Per_Capita, Period
IFF (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;

Still gets "syntax error (missing operator) in a query expression ..."

What is the question? What did you try so far? Please read this: http://stackoverflow.com/help/how-to-ask — Pred, Jun 03 '14 at 16:22
hi, i have function averege if in Excel and i would like to do the same in sql/ms access query. — user3703785, Jun 04 '14 at 06:54
I suggest you to update your question, add more details and share what you tried, and what problems appeared since you tried to solve the problem. — Pred, Jun 04 '14 at 07:01
i see your point, thanks. I updated question. This was my first post..sorry — user3703785, Jun 04 '14 at 07:31

StayPuft · Answer 1 · 2014-06-04T15:17:03.867

0

I think I know what you're asking for and I believe this is what you're looking for -

Select
    Costs_Per_Capita,
    Related_Period_ID,
    (Select 
        Case when Related_Period_ID = 1 then 
        (Select
            AVG(Costs_Per_Capita)
            From Costs_Per_Capita_Table
            Where Related_Period_ID = 1)
        else 
       (Select
            AVG(Costs_Per_Capita)
            From Costs_Per_Capita_Table
            Where Related_Period_ID = 2)
        END 
    )
    From
    Costs_Per_Capita_Table

Changing CASE to IFF

Select 
        IFF (Related_Period_ID = 1, 
        (Select
            AVG(Costs_Per_Capita)
            From Costs_Per_Capita_Table
            Where Related_Period_ID = 1),
       (Select
            AVG(Costs_Per_Capita)
            From Costs_Per_Capita_Table
            Where Related_Period_ID = 2)

edited Jun 04 '14 at 15:17

answered Jun 03 '14 at 17:10

StayPuft

125
1
1
13

thanks! statement seems like what i need. I just modified names of table and columns like that: Select Costs_Per_Capita, Period, (Select Case when Period = 1 then (Select AVG(Costs_Per_Capita) From Costs Where Period = 1) else (Select AVG(Costs_Per_Capita) From Costs Where Period = 2) END) From Costs I got a error message "syntax error in query expression – user3703785 Jun 04 '14 at 06:48
AH! Well the error is definitely in the fact that I was using SQL language and not Access language - you might need to rewrite it according to the syntax of the Access language (rather than using SQL that I used here) - but the idea, I would assume, is basically the same - If Period is 1 then use some avg function for Capita if period is 2 then use avg function for Capita – StayPuft Jun 04 '14 at 15:06
Well looking at this the syntax looks pretty much the same: http://office.microsoft.com/en-us/access-help/access-sql-basic-concepts-vocabulary-and-syntax-HA010256402.aspx – StayPuft Jun 04 '14 at 15:09
Perhaps you're missing the ; at the end of the query – StayPuft Jun 04 '14 at 15:09
And doing some MORE research - CASE statement in SQL - is IFF statement in Access so I believe that might be the real problem in all this: http://stackoverflow.com/questions/772461/case-statement-in-access – StayPuft Jun 04 '14 at 15:11
i used statement as you recommended but still gets error, see my statement above. I appreciate your help so much! – user3703785 Jun 05 '14 at 11:36

score 0 · Answer 2 · answered Jun 05 '14 at 12:52

I did this with: SELECT Costs.Costs_Per_Capita, Costs.Period,

IIF(Costs.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;

other way, better if more periods apper is using subquery:

SELECT Costs.Period, Costs.Costs_Per_Capita, tmpQry.CALCULATED_Period_Avg_Costs

FROM Costs INNER JOIN (SELECT Costs.Period, Avg(Costs.Costs_Per_Capita)

AS CALCULATED_Period_Avg_Costs FROM Costs

GROUP BY Costs.Period) AS tmpQry ON Costs.Period = tmpQry.Period;

maybe someone will use it later on...

score 0 · Answer 3 · edited May 04 '17 at 03:05

0

Select CostsPerCapital, Period, avg(CostPerCapita) over (partition by Period)
From Table
Where....
Order by ...

edited May 04 '17 at 03:05

Pang

9,564
146
81
122

answered May 04 '17 at 02:27

User1234098

51
1
3

How do I get Average IF statement in SQL?

3 Answers3