SQL Left select, to get a zero when no data is returned

Try making it a left outer join.

...
Left outer join SITE_INCOME si
...

Without knowing your data structure I can't be certain but...

What I'm seeing is that your LEFT JOIN for SITE_INCOME is dependent upon two INNER JOINS on LOCATION_GROUPS and locations. My guess is that your join is omitting the record you want because of one or both of those inner joins.

@slightwv - All LEFT joins are OUTER joins. The OUTER keyword is optional.

>>The OUTER keyword is optional.

Thanks for the correction.  There is the one new thing I learned today...  I can stop learning for the day!

When you do this in the WHERE clause

 AND si.si_year = 2016
 AND si.si_week BETWEEN 10 AND 10


you are effectively negating the OUTER JOIN, and turning it into an INNER JOIN.

The reason this happens is the WHERE clause is applied after the join conditions are resolved.

So, if you don't have an SI row from the join, you will get NULL values returned by the outer join,
But, your where clause will then reject those rows because NULL can never satisfy those conditions.

The same is true for conditions that compare results between tables in the where clause; but the explicit value comparisons make it more obvious
To fix this,  move those conditions from the WHERE into the ON conditions of the outer join

Try this -

Select DH.DH_Position,L.LOC_NAME, DH.DH_Name,
[b]sum(si.si_income1[/b]) as Value
From LOCATION_GROUP_TYPES gt
inner join LOCATION_GROUPS g
on gt.LGT_ID = g.LG_LGT_ID
inner join locations l
on g.LG_LOC_ID = l.LOC_ID
[b]left join
(select si_loc_id, nvl(si_income1,0) si_income1
 from site_income) si
[/b]on si.si_loc_id = l.loc_id
inner join Department_Headings DH
on DH.DH_Position = 1
Where (LOC_DATE_CLOSED is null or to_char(LOC_DATE_CLOSED,'YYYY') >= 2016)
and gt.LGT_REGION is not null
and si.si_year =  2016
and si.si_week Between 10 and 10 and gt.LGT_DESCRIPTION = 'NORTH WEST AND NORTH MIDLANDS' and L.LOC_NAME = 'CHORLTON' 
group by DH.DH_Position,L.LOC_NAME, DH.DH_Name;

Select allOpen in new window

Apparently bold can not be embedded in code, which is what I attempted to do. I also didn't notice that the references to si.year and si.week would have to be included in the subquery aliased as si.

I hate messing up so badly but the si. qualifier should be removed from the subquery :-(

using the subquery method doesn't help

LEFT JOIN
(select si_loc_id, nvl(si_income1,0) si_income1

Select allOpen in new window

doesn't address the problem.
if there is no row, the NVL won't be executed, so a 0 won't be generated

inner join Department_Headings DH on DH.DH_Position = 1

How many rows does this return?
   if only 1 then I would suggest using CROSS JOIN instead
   if it can return more than one it will multiply the SUM() results

Where (LOC_DATE_CLOSED is null or to_char(LOC_DATE_CLOSED,'YYYY') >= 2016)

I assume this column comes from Locations L and I assume it is a date
to_char(...'YYYY') returns a string
2016 is an integer

so you are converting a date to a string and comparing to an integer
that isn't efficient

this would be more efficient
Where (L.LOC_DATE_CLOSED is null or L.LOC_DATE_CLOSED >= to_date('2016-01-01','YYYY-MM-DD')

(if L.LOC_DATE_CLOSED is a timestamp then use to_timestamp()


and your original where clause directly references the left joined table,

and si.si_year =  2016
and si.si_week Between 10 and 10

so EVERY ROW MUST meet those 2 predicates, and hence NULL from that table is suppressed

To fix this either alter the where clause like this:

and ( (si.si_year =  2016 and si.si_week Between 10 and 10) OR si.si_year IS NULL)

OR (preferred) move those predicates into the join (this is shown in comments above)

Thank you for your help.