title: Exclude records in AVG SQL function? (can't use WHERE in main query because of another count)
body: <p>my SQL query which works fine is</p>
  
  <pre><code>select case month(timestamp_iso(STATUSDATE))
          when 1 then 'January'
          when 2 then 'February'
          when 3 then 'March'
          when 4 then 'April'
          when 5 then 'May'
          when 6 then 'Jun'
          when 7 then 'July'
          when 8 then 'August'
          when 9 then 'September' 
          when 10 then 'October'
          when 11 then 'November'
          when 12 then 'December'
      end as Month, 
  
      count (case when service='ADSL' then 1 end) as  ADSL,
    AVG (timestampdiff(
    4, 
    char(actualfinish - reportdate))/60.00) as efficiecny
  
  
  
  from INCIDENT
  where   year(STATUSDATE) = year(current_timestamp) 
  group by month(timestamp_iso(STATUSDATE))
  </code></pre>
  
  <p>I want to get for each month number of services with ADSL (it is done through the first COUNT)
  and average time difference in hours for the records which do not have service ADSL. So I must exclude in the AVG function all records with service ADSL but I can not put it in where clause    </p>
  
  <pre><code> where   year(STATUSDATE) = year(current_timestamp) and service!='ADSL' 
  </code></pre>
  
  <p>because my COUNT function needs to  have service='ADSL'</p>
  
  <p>How to solve this?</p>
  
  <p>Thanks</p>