So this may seem like a silly question, but I am attempting to show data for certain periods of time for each fiscal year. However, some of the fiscal years do not have data for some of those periods of time, but the users would like to see zeros for them.
I suppose a summary of what I'm asking could be, how could I go about putting rows in my answerset that don't have any data to go with them?
Please feel free to ask any questions that would better help you understand what I'm looking for.
select c.theYear as "Year", coalesce(total_sales,0) as total_sales from (select distinct year_of_calendar from sys_calendar.calendar where year_of_calendar between 2008 and 2011) as c (theYear) left join (select sum(theAmount), extract(year from theDate) as theYear from sales group by 2) as sales_sum (total_sales, theYear) on sales_sum.theYear = c.theYear order by 1 desc ;
*** Query completed. 4 rows found. 2 columns returned. *** Total elapsed time was 1 second.