title: SQLite: Using avg and case together
body: <p>I have a table structured like this:</p>
  
  <pre><code>id | date       | count
  ---+------------+-------
  0  | 01-01-2000 | 46
  1  | 01-01-2000 | 25
  ...
  0  | 01-02-2000 | 235
  1  | 01-02-2000 | 23
  ...
  </code></pre>
  
  <p>And so on.  I'd like to create a table (solely for presentation purposes, there's clearly no reason to store the data like this) that shows averages for each id for each day of the week.  As an example, I could show the <i>sum</i> for each day of the week with something like this:</p>
  
  <pre><code>id | sun | mon | tues | wed | thur | fri | sat |
  ---+-----+-----+------+-----+------+-----+-----+
  0  | 146 | 13  | 51   | 123 | ...
  1  | 225 | 245 | 2367 | 25  | ...
  ...
  0  | 235 | 246 | 25   | ....
  1  | 23  | .....
  ...
  
  CREATE TABLE dailySum AS
  SELECT id,
         SUM(CASE WHEN CAST(strftime('%w', date) AS INTEGER) = 0 THEN count ELSE 0 END) as sun,
         SUM(CASE WHEN CAST(strftime('%w', date) AS INTEGER) = 1 THEN count ELSE 0 END) as mon,
         SUM(CASE WHEN CAST(strftime('%w', date) AS INTEGER) = 2 THEN count ELSE 0 END) as tues,
         SUM(CASE WHEN CAST(strftime('%w', date) AS INTEGER) = 3 THEN count ELSE 0 END) as wed,
         SUM(CASE WHEN CAST(strftime('%w', date) AS INTEGER) = 4 THEN count ELSE 0 END) as thur,
         SUM(CASE WHEN CAST(strftime('%w', date) AS INTEGER) = 5 THEN count ELSE 0 END) as fri,
         SUM(CASE WHEN CAST(strftime('%w', date) AS INTEGER) = 6 THEN count ELSE 0 END) as sat
  FROM tableOfImportantThings
  GROUP BY id;
  </code></pre>
  
  <p>However, if I tried the same thing with averages, then I'd be including all of those zeroes in my calculation of the average, which would clearly lower it dramatically.  I suppose I could get a count of distinct dates for each day of the week and divide by that count in a later query, but solutions like that seem overly complicated.  I'm certain I'm missing something obvious.  Any suggestions?</p>