title: MySQl - AVG ( AVG IF) & group by
body: <p>I use MySQL. I have data in the following format - </p>
  
  <pre><code>DATE     , PAGE , PAGE_LOAD_DURATION , VISIT_TYPE
  01-01-15 , A    ,                  6 , AM
  01-01-15 , B    ,                  4 , PM
  01-01-15 , C    ,                  5 , AM
  01-01-15 , D    ,                  4 , AM
  02-01-15 , B    ,                 13 , PM
  03-01-15 , C    ,                 15 , PM
  04-01-15 , A    ,                 17 , PM
  05-01-15 , A    ,                 34 , PM
  06-01-15 , B    ,                 56 , AM
  07-01-15 , C    ,                 12 , AM
  08-01-15 , D    ,                 89 , AM
  09-01-15 , B    ,                 34 , AM
  10-01-15 , R    ,                 12 , PM
  11-01-15 , T    ,                 56 , AM
  12-01-15 , E    ,                 78 , PM
  13-01-15 , B    ,                 90 , AM
  14-01-15 , A    ,                 34 , PM
  15-01-15 , A    ,                 56 , AM
  16-01-15 , B    ,                 23 , AM
  17-01-15 , A    ,                 15 , PM
  18-01-15 , R    ,                 17 , AM
  19-01-15 , Y    ,                 37 , PM
  20-01-15 , C    ,                 23 , AM
  </code></pre>
  
  <p>I'm trying to generate the average page load duration per page per visit type for a given date range</p>
  
  <p>I'd like to generate the output in the following format -</p>
  
  <pre><code>PAGE, VISIT_TYPE, AVERAGE_PAGE_LOAD_DUR
  A, AM, average_page_load_dur
  A, PM, average_page_load_dur
  B, AM, average_page_load_dur
  B, PM, average_page_load_dur
  C, AM, average_page_load_dur
  C, PM, average_page_load_dur
  D, AM, average_page_load_dur
  D, PM, average_page_load_dur
  so on
  </code></pre>
  
  <p>To calculate the SUM instead of average, I use the below query - </p>
  
  <pre><code>select
      PAGE,
      SUM(IF(VISIT_TYPE = 'AM',AVERAGE_PAGE_LOAD_DUR,0)) AS SUM_AM,
      SUM(IF(VISIT_TYPE = 'PM',AVERAGE_PAGE_LOAD_DUR,0)) AS SUM_PM
  from
          PAGE_VISITS
  where
      DATE &gt;= '01-01-15' and DATE &lt;= '09-01-15'
  group by
      PAGE
  </code></pre>
  
  <p>To calcualte average, I replaced SUM with AVG, but, it looks like it is not the correct way to do as the average is being calculated as every row is scanned which doesn't make sense ( This is how I think it works. Please correct me if otherwise)</p>
  
  <pre><code>select
      PAGE,
      AVG(IF(VISIT_TYPE = 'AM',AVERAGE_PAGE_LOAD_DUR,0)) AS SUM_AM,
      AVG(IF(VISIT_TYPE = 'PM',AVERAGE_PAGE_LOAD_DUR,0)) AS SUM_PM
  from
          PAGE_VISITS
  where
      DATE &gt;= '01-01-15' and DATE &lt;= '09-01-15'
  group by
      PAGE
  </code></pre>
  
  <p>How could I get the average to work please?</p>