How to deal with counting items by date in MySQL when the counter for a given date increment is 0?
I want to make some histograms for counting items by day, month and year. The problem I'm running into is that my simple MySQL queries only return numbers that have values to count. It does not magically fill in dates when dates do not 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 are not represented, only weeks when items were sold are represented.
My tables and fields look like this:
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 first query I mentioned 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 read that I have to join a calendar table with tables where the counting takes place. I did this, but now I cannot get the first query to reproduce this second query because the join in the first query excludes dates from the query result when product sales are 0.
here's the second query to be combined with the first query to somehow get 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 error in this second query is that it does not account for item_sales from
(person trying to sell an item to users
). The first query is executed, but it does not have all the dates when the item is sold = 0. So yes, disappointing.
This is how I would like the resulting data to look (NOTE: this is what account_id = 125 sold, other people have a lot of different numbers over this time period):
2012-01-01 1 2012-01-08 1 2012-01-15 0 2012-01-22 2 2012-01-29 0
This is what the first request 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 very grateful.
source to share
I'm not entirely sure about the problem you are getting as I am not aware of the actual tables and the data they contain that generate these results (which would help a lot!). However, try something. Use this condition:
where (items.account_id = 125 or items.account_id is null) and (other-conditions)
source to share
Your first request is fine. The point is that you have no data in the mysql table and therefore it cannot group the data together. It is perfectly. You can explain this in your code so that if the date does not exist then there is obviously no data for the graph. You can better account for this by ordering the date value so you can loop through accordingly and look for missing days.
Also, to avoid executing the DATE () function, you can change the GROUP BY date to GROUP BY (because your fields have DATE (pruchase_date) selected as the date)
source to share