1

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.

Community
  • 1
  • 1
Ajay Punja
  • 101
  • 4
  • 11
  • "get June and August to display" That sound to like you have another table or some configuration where all the months are stored. If not, you also can't display them if they are empty, because at no point your software would about those months. – feeela Dec 04 '12 at 14:48
  • 2
    It'd be good if you post your tables schema and some sample data. – Nathan Dec 04 '12 at 15:59

1 Answers1

2

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
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
bobwienholt
  • 17,420
  • 3
  • 40
  • 48
  • I've just did it thank you. But with the LEFT JOIN in play, my times_of_sale that I summed up are now displaying (NULL). I tried ISNULL but that returns 1's. I tested it out before, and my summed up time of sales yields digits from TIME_FORMAT. How can I re-display my time again? I tried outer join but that doesn't work. – Ajay Punja Dec 04 '12 at 16:02
  • It would help if you posted your table structure and the query you are currently using. – bobwienholt Dec 04 '12 at 16:03
  • figured it out, sorry, it was some stupid problem! Thanks for your help :) – Ajay Punja Dec 04 '12 at 16:24
  • 1
    When using outer joins, `COUNT(*)` and `COUNT(1)` will give 1 instead of 0 for the months without sales. – ypercubeᵀᴹ Dec 04 '12 at 16:34