<div>
Try making it a left outer join. 
 
... 
Left outer join SITE_INCOME si 
...
</div>

<div>
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.
</div>

<div>
&gt;&gt;The OUTER keyword is optional. 
 
Thanks for the correction. &nbsp;There is the one new thing I learned today... &nbsp;I can stop learning for the day!
</div>

<div>
When you do this in the WHERE clause 
 
&nbsp;AND si.si_year = 2016 
&nbsp;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, &nbsp;move those conditions from the WHERE into the ON conditions of the outer join
</div>

<div>
Try this - 
 

<pre><code class="code-10 nolinks" id="code-20-41506454-1">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') &gt;= 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;
</code></pre>
</div>

<div>
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.
</div>

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

<div>
using the subquery method doesn't help 
 

<pre><code class="code-1 nolinks" id="code-20-41506476-1">LEFT JOIN
(select si_loc_id, nvl(si_income1,0) si_income1
</code></pre>
doesn't address the problem. 
if there is no row, the NVL won't be executed, so a 0 won't be generated
</div>

<div>
inner join Department_Headings DH on DH.DH_Position = 1 
 
How many rows does this return? 
&nbsp; &nbsp;if only 1 then I would suggest using CROSS JOIN instead 
&nbsp; &nbsp;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') &gt;= 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 &gt;= to_date('2016-01-01','YYYY-MM-DD') 
 
(if L.LOC_DATE_CLOSED is a timestamp then use <a href="https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions193.htm" rel="ugc">to_timestamp()</a> 
 
 
and your original where clause directly references the left joined table, 
 
and si.si_year = &nbsp;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 = &nbsp;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)
</div>

<div>
<div class="content wysiwyg-content">
Hello Experts Exchange 
I am using Oracle to select data from a database to get a sum of a value, but I want a record when no data is returned. 
 
This is my query; 

<pre><code class="code-10 nolinks" id="code-10-28932954-1">Select DH.DH_Position,L.LOC_NAME, DH.DH_Name,
sum(NVL(si.si_income1,0)) 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
Left join SITE_INCOME si
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') &gt;= 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
</code></pre>
 
But the query does not work, how do I change so that if no data in the SITE_INCOME table I still get a record stating zero. 
 
Regards 
 
SQLSearcher
</div>
</div>

Hello Experts Exchange
I am using Oracle to select data from a database to get a sum of a value, but I want a record when no data is returned.

This is my query;
(CODE)

But the query does not work, how do I change so that if no data in the SITE_INCOME table I still get a record stating zero.

Regards

SQLSearcher

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

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

Oracle Database

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.