SQL SUM return 0 where no rows found

Hello Experts Exchange
I am running a query on Oracle where there is no data I want it to return zero or Null.

This is my query.
Select DH.DH_Name,Z.loc_name,
sum(NVL(si.si_income1,0)) as Value
From (select ROW_NUMBER () OVER (partition by LGT_DISPLAY_ORD ORDER BY LGT_DISPLAY_ORD DESC ) as position, 
        l.loc_id, 
        l.loc_loc_code, 
        l.loc_name, 
        t.LGT_SHORT_CODE, 
        t.LGT_DESCRIPTION as area, 
        t.LGT_REGION, 
        l.LOC_DATE_OPENED, 
        l.LOC_DATE_CLOSED, 
        store_status( l.LOC_DATE_OPENED, l.LOC_DATE_CLOSED) as status
        from LOCATION_GROUP_TYPES t, LOCATION_GROUPS g, locations l
        where t.LGT_TYPE = 'R' 
        and t.LGT_ID = g.LG_LGT_ID 
        and LG_LOC_ID = l.LOC_ID
        and (LOC_DATE_CLOSED is null or to_char(LOC_DATE_CLOSED,'YYYY')  = 2015)
        and l.loc_loc_ty_id = 4   
        order by t.LGT_DISPLAY_ORD, l.loc_loc_code) Z
inner join SITE_INCOME si
on si.si_loc_id = Z.loc_id
inner join Department_Headings DH
on DH.DH_Position = 1
Where si.si_year =  2015
and si.si_week Between 24 and 24
AND Z.position = 6 and Z.Area = 'NORTH EAST'
group by DH.DH_Name,Z.loc_name

Open in new window


There is defiantly data for DH.DH_Name and Z.loc_name columns, but there is no data for it to sum up, I want it to return a null or zero with the  DH.DH_Name and Z.loc_name columns how do I change my code so that I get what I am looking for?

Regards

SQLSearcher
SQLSearcherAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
>>There is defiantly data for DH.DH_Name and Z.loc_name columns, but there is no data for it to sum up

I don't understand how there can be data and not be data at the same time.

Do you mean that if you remove the join to SITE_INCOME, data is returned?

If so, try making it a left outer join instead of an inner join.
SQLSearcherAuthor Commented:
Hi Slightwv
I have tried Left Outer join but it does not work.

When I say there defiantly data for DH.DH_Name and Z.loc_name columns, in the DH.DH_Name it should say Womensware and the Z.loc_name  should say PITLOCHRY, but the where clause is trying to select data where there is none, that's why I want it to say zero or null.

Regards

SQLSearcher
slightwv (䄆 Netminder) Commented:
A select statement cannot make up data when there is none.

You will need adjust the where clause to actually return ALL available products, then sum based on other criteria.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

slightwv (䄆 Netminder) Commented:
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
An outer join will work, and is what you need to use. Keep in mind that joins are processed left to right, if you do not use parens to change that. Having the table with the "optional" values as first table can lead to an effective inner join if you do not take care.
More, the "optional" table is SITE_INCOME si here, and you use the value in WHERE. So if that is the outer-joined table, the WHERE converts it into an inner join.
slightwv (䄆 Netminder) Commented:
To get the 0 when it is null, was answered in the next question:
http://www.experts-exchange.com/questions/28690479/SQL-to-select-Category-and-Null-value.html

I feel that since those have accepted answers, this question is really a duplicate.
SQLSearcherAuthor Commented:
Hello Qlemo
How should I change my query to get it to work?

Regards

SQLSearcher
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Since I don't know what you are after exactly, I cannot tell you. But I assume you want to do
Select DH.DH_Name,Z.loc_name,
sum(NVL(si.si_income1,0)) as Value
From (select ROW_NUMBER () OVER (partition by LGT_DISPLAY_ORD ORDER BY LGT_DISPLAY_ORD DESC ) as position, 
        l.loc_id, 
        l.loc_loc_code, 
        l.loc_name, 
        t.LGT_SHORT_CODE, 
        t.LGT_DESCRIPTION as area, 
        t.LGT_REGION, 
        l.LOC_DATE_OPENED, 
        l.LOC_DATE_CLOSED, 
        store_status( l.LOC_DATE_OPENED, l.LOC_DATE_CLOSED) as status
        from LOCATION_GROUP_TYPES t, LOCATION_GROUPS g, locations l
        where t.LGT_TYPE = 'R' 
        and t.LGT_ID = g.LG_LGT_ID 
        and LG_LOC_ID = l.LOC_ID
        and (LOC_DATE_CLOSED is null or to_char(LOC_DATE_CLOSED,'YYYY')  = 2015)
        and l.loc_loc_ty_id = 4) Z
left join SITE_INCOME si
  on si.si_loc_id = Z.loc_id
  and si.si_year =  2015
  and si.si_week Between 24 and 24
inner join Department_Headings DH
on DH.DH_Position = 1
Where Z.position = 6 and Z.Area = 'NORTH EAST'
group by DH.DH_Name,Z.loc_name

Open in new window

Note that the ORDER BY in the in-line table is invalid, and other DBMS' would have told you so. It does not makes sense anyway, because an in-line table is never sorted.

I cannot tell whether the duplicate question claim of Steve is correct :/

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Alex [***Alex140181***]Software DeveloperCommented:
Apart from all what's already been mentioned above, I really like to comment upon a few fundamental SQL "Don't"s:

1. sum(NVL(si.si_income1,0)) as Value
The use of NVL here makes absolutely no sense. NULL values do not matter when using SUM!

2. and LG_LOC_ID = l.LOC_ID
If you're using table (or view) aliases, then please use them throughout the entire statement!

3. and (LOC_DATE_CLOSED is null or to_char(LOC_DATE_CLOSED,'YYYY')  = 2015)
First: Try to avoid this kind of implicit type conversion (here comparing a string with a number)! Things might get really nasty considering the CBO finding the right way down the indexes ;-)
Second: There are numerous built-in functions that could help a lot, like LNNVL: and LNNVL(to_char(LOC_DATE_CLOSED,'YYYY')  != '2015')


4. and si.si_week Between 24 and 24
Why the "BETWEEN"?! Just do an eq: "and si.si_week = 24"
SQLSearcherAuthor Commented:
Thank you Qlemo for all your help it was just what I was after.
johnsoneSenior Oracle DBACommented:
Just to comment on Alexander's point #1.

The NVL within the SUM is meaningful and it totally makes sense.

If you add 1 + 2 + null + 3 + null, you get 6 and the nulls don't affect the outcome.

However, if you add null + null, you get null.  I'm betting they want the 0 in this case.

Now, to cut down on the function call to NVL, you should probably do it this way nvl(sum(si.si_income1),0) , but it is still a valid and meaningful syntax either way.
Alex [***Alex140181***]Software DeveloperCommented:
Yes, indeed, putting the NVL "in front"/around the SUMmarized value would absolutely make sense THAT way ;-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.