title: Best way in MySQL or Rails to get AVG per day within a specific date range
body: <p>I'm trying to make a graph in Rails, for example the avg sales amount per day for each day in a given date range</p>
  
  <p>Say I have a products_sold model which has a "sales_price" float attribute. But if a specific day has no sales (e.g none in the model/db), I want to return simply 0.</p>
  
  <p>What's the best way in MySQL/Rails to get this done?  I know I can do something like this:</p>
  
  <p>(<em>This SQL query might be the completely wrong way to get what I'm wanting too</em>)</p>
  
  <pre><code>SELECT avg(sales_price) AS avg, DATE_FORMAT(created_at, '%m-%d-%Y') AS date
      FROM products_sold WHERE merchant_id = 1 GROUP BY date;
  </code></pre>
  
  <p>And get results like this:</p>
  
  <pre>
  | avg |    date    |
    23    01-03-2009
    50    01-05-2009 
    34    01-07-2009
    ...       ...
  </pre>
  
  <p>What I'd like to get is this:</p>
  
  <pre>
  | avg |    date    |
    23    01-03-2009
     0    01-04-2009
    50    01-05-2009
     0    01-06-2009 
    34    01-07-2009
     0    01-08-2009
    ...       ...
  </pre>
  
  <p>Can I do this with SQL or will I have to post-process the results to find what dates in the daterange aren't in the SQL result set? Perhaps I need some sub-selects or IF statements?</p>
  
  <p>Thanks for any help everyone.</p>