SQLSearcher
asked on
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.
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
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
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
ASKER
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
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
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.
You will need adjust the where clause to actually return ALL available products, then sum based on other criteria.
I don't see how this question is different from your previous question:
https://www.experts-exchange.com/questions/28690222/SQL-category-is-in-the-returned-data-when-no-data-is-for-that-Category.html
https://www.experts-exchange.com/questions/28690222/SQL-category-is-in-the-returned-data-when-no-data-is-for-that-Category.html
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.
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.
To get the 0 when it is null, was answered in the next question:
https://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.
https://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.
ASKER
Hello Qlemo
How should I change my query to get it to work?
Regards
SQLSearcher
How should I change my query to get it to work?
Regards
SQLSearcher
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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,'Y YYY') = 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_CLO SED,'YYYY' ) != '2015')
4. and si.si_week Between 24 and 24
Why the "BETWEEN"?! Just do an eq: "and si.si_week = 24"
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,'Y
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_CLO
4. and si.si_week Between 24 and 24
Why the "BETWEEN"?! Just do an eq: "and si.si_week = 24"
ASKER
Thank you Qlemo for all your help it was just what I was after.
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.
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.
Yes, indeed, putting the NVL "in front"/around the SUMmarized value would absolutely make sense THAT way ;-)
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.