Hey guys,
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
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.
What's the best way in MySQL/Rails to get this done? I know I can do something like this:
(This SQL query might be the completely wrong way to get what I'm wanting too)
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;
And get results like this:
| avg | date | 23 01-03-2009 50 01-05-2009 34 01-07-2009 ... ...
What I'd like to get is this:
| 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 ... ...
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?
Thanks for any help everyone.