SQL for monthly report with each month must be selected even if there is no data

[manoj r patil]

I have one requirement: I want to generate a monthly report for a period but the catch is that each month must be selected. So if the period is say 12 months, it must return 12 rows no matter whether data is there or not for those months.

I have given the sample which I am using currently but I need to mold it to satisfy the above requirement.

select SUM(Hours), YEAR(trx_date) as yr, Month(trx_date) as monthno from laborhist where trx_date >= '01/01/09' and trx_date <= '31/12/09' group by 2, 3

Any idea how can we write this SQL?

[Tim McGuire]

You want to have a left outer join with a table that contains all the months.