title: SQL Server - use case statement within group by
body: <p>I am trying to create a view as follows:</p>
  
  <pre><code>CREATE VIEW vw1 AS SELECT     
    Town
  , case when (Spend &gt; 0 and NbOrders &gt; 0)
         then sum(Spend)/sum(NbOrders)
         else null
         end as AvgSpend
  , case when (Margin &gt; 0 and NbOrders &gt; 0)
         then sum(Margin)/sum(NbOrders)
         else null
         end as AvgMargin
  FROM Table
  group by Town
  </code></pre>
  
  <p>But I get an error because the fields used in my 'case when...' statement aren't included in an aggregate function.</p>
  
  <p>I can't use a 'where' clause because the average fields depend on different fields being >0 and I can't see how I could make the case when work on an aggregated field?</p>
  
  <p>Any ideas how I can achieve this - preferably in a single statement?</p>