Possible Duplicate:
Displaying zero valued months with SQL
For a certain attribute, is it possible to display its rows even if its empty?
Say I have attribute date_of_car_sale. Also, let's say I want to do a monthly report.
From January to December of 2009, there's two months where the company has been away on holiday, so there's no date_of_car_sale for June and August.
Is it possible to make a report that displays every month of 2009, even if June and August has no data value?
I can make every month show and by grouping them to show individual months only. But can't seem to get June and August to display because they're empty.
PS: This only requires one column from the table Company.
In cases like this, I usually make a table with all values I want displayed and do a left join to my data set. So in your case, I would make a table called "months" with a single date column that stores the first day of the month or maybe two columns with the first and last day and do join like this:
SELECT m.month_first, COUNT(x.date_of_car_sale) as sales
FROM months m
LEFT JOIN (
SELECT date_of_car_sale
FROM sales
) x ON m.month_first <= x.date_of_car_sale
AND m.month_last >= x.date_of_car_sale
GROUP BY m.month_first
ORDER BY m.month_first