SQL - Return Null Value on Join Rather than no result

Question

I need to figure out a way to return a value for all months even when there is no match. For instance I currently have data for September through January but since there were no views for September or October, then there was no match because the dataset does not show a value if there were no page views.

What I need is to show null or 0 where there is no match, rather than just having no result.

SELECT NULL LINK,
       u.page_name,
       TO_CHAR( s.data_date, 'Month-YYYY') as "Month",
       COALESCE(SUM(s.page_views),0) as "Product Pages"
from            URL_RELATE u
FULL OUTER JOIN SITE_DATA  s on SUBSTR(u.url,38,7) = SUBSTR(s.page,22,7)
                             or u.url = s.page
Where u.page_name = :P22_PRODUCT_PAGES_1
  and u.TRC_CRC = 'TRC'
group by s.data_date,
         u.page_name
order by s.data_date

score 1 · Answer 1 · edited May 23 '17 at 12:31

Your easiest bet (in my opinion) is probably to have a calendar table (i've used a "holiday table" in the past, where if i need all dates, days, months, whatever). You simply left join onto data table from the calendar table with the appropriate columns and you'll have your all your months, rather than just the months with values.

Here's an example of the creation of a calendar table:

How to create a Calender table for 100 years in Sql

SQL - Return Null Value on Join Rather than no result

1 Answers1