How to deal with counting items by date in MySQL when the count for a given date increment is 0?

Question

I'm looking to make some bar graphs to count item sales by day, month, and year. The problem that I'm encountering is that my simple MySQL queries only return counts where there are values to count. It doesn't magically fill in dates where dates don't exist and item sales=0. This is causing me problems when trying to populate a table, for example, because all weeks in a given year aren't represented, only the weeks where items were sold are represented.

My tables and fields are as follows:

items table: account_id and item_id 
// table keeping track of owners' items
items_purchased table: purchaser_account_id, item_id, purchase_date
// table keeping track of purchases by other users
calendar table: datefield
//table with all the dates incremented every day for many years

here's the 1st query I was referring to above:

 SELECT COUNT(*) as item_sales, DATE(purchase_date) as date
 FROM items_purchased join items on items_purchased.item_id=items.item_id 
 where items.account_id=125
 GROUP BY DATE(purchase_date) 

I've read that I should join a calendar table with the tables where the counting takes place. I've done that but now I can't get the first query to play nice this 2nd query because the join in the first query eliminates dates from the query result where item sales are 0.

here's the 2nd query which needs to be merged with the 1st query somehow to produce the results i'm looking for:

SELECT calendar.datefield AS date, IFNULL(SUM(purchaseyesno),0) AS item_sales
FROM items_purchased join items on items_purchased.item_id=items.item_id 
RIGHT JOIN calendar ON (DATE(items_purchased.purchase_date) = calendar.datefield)
WHERE (calendar.datefield BETWEEN (SELECT MIN(DATE(purchase_date)) 
FROM items_purchased) AND (SELECT MAX(DATE(purchase_date)) FROM items_purchased)) 
GROUP BY date
// this lists the sales/day
// to make it per week, change the group by to this: GROUP BY week(date)

The failure of this 2nd query is that it doesn't count item_sales by account_id (the person trying to sell the item to the purchaser_account_id users). The 1st query does but it doesn't have all dates where the item sales=0. So yeah, frustrating.

Here's how I'd like the resulting data to look (NOTE: these are what account_id=125 has sold, other people many have different numbers during this time frame):

2012-01-01     1
2012-01-08     1
2012-01-15     0
2012-01-22     2
2012-01-29     0

Here's what the 1st query current looks like:

2012-01-01     1
2012-01-08     1
2012-01-22     2

If someone could provide some advice on this I would be hugely grateful.

Answer 1

I'm not quite sure about the problem you're getting as I don't know the actual tables and data they contain that generates those results (that would help a lot!). However, let's try something. Use this condition:

where (items.account_id = 125 or items.account_id is null) and (other-conditions)

Answer 2

Your first query is perfectly acceptable. The fact is you don't have data in the mysql table and therefore it can't group any data together. This is fine. You can account for this in your code so that if the date does not exist, then obviously there's no data to graph. You can better account for this by ordering the date value so you can loop through it accordingly and look for missed days.

Also, to avoid doing the DATE() function, you can change the GROUP BY to GROUP BY date (because you have in your fields selected DATE(pruchase_date) as date)