title: How do I get Average IF statement in SQL?
body: <p>I have table in MS Access with columns Year, Period (values just <code>1</code> and <code>2</code>), Costs_Per_Capita and CALCULATED_Period_Avg_Costs, PK is ID. </p>
  
  <p>I need to calculate <code>CALCULATED_Period_Avg_Costs</code>. It should return average of <code>Costs_Per_Capita</code> for given period from <code>Period</code> column. In Excel I do this with SUMIF/COUNTIF which returns Average IF.</p>
  
  <p>Do you have some advice how to write a code in SQL to do that?</p>
  
  <p>Results should look like:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I used statement: </p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Still gets "syntax error (missing operator) in a query expression ..."</p>