• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 131
  • Last Modified:

Warning: Null value is eliminated by an aggregate or other SET operation

How do I show the NULL values and not eliminate them when aggregating.
I'm joining on a lookup table and want to show all the lookup table values even though their are not values associated with that value.

Any ideas?
0
JElster
Asked:
JElster
3 Solutions
 
Mike EghtebasDatabase and Application DeveloperCommented:
If you do not have where clause add it:

Where CustName ='ABC' or CustName Is Null

BTW, null value is not correct us. We could say either null or null mark.

This is because null is null because it has no value and it is never going to get one.

This is not my pinion or interpretation. It comes from a book I am studying now.
0
 
JElsterAuthor Commented:
I want to show ZERO'S where the value is null... for the sum of the values with no corresponding lookup value
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
That should be handled in SELECT portion:

Select Sum(IsNull(Amount,0))
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
HuaMinChenBusiness AnalystCommented:
Put like
...
sum(isnull(column1,0))
...

Open in new window

0
 
Mike EghtebasDatabase and Application DeveloperCommented:
HuaMinChen,

Is this different than my post is some way?

Mike
0
 
PortletPaulCommented:
Your question is quite broad.  
While I believe eghtebas has correctly pointed to the ISNULL() function this might not be all you need.

How do I show the NULL values and not eliminate them when aggregating.
I'm joining on a lookup table and want to show all the lookup table values

You might need an outer join to "show" information that would be ignored if using an INNER JOIN

select lookup.name, COALESCE(count(d.id),0) as times_used
from lookup
LEFT OUTER JOIN your_data_table d on lookup.id = d.lookupid
group by lookup.name

This would list ALL lookup names even if they haven't been used in the data table.

Notes:
When using COUNT() it can be very important that NULLs do exist (because NULL is ignored in the count)

As an alternative to ISNULL()  you can use COALESCE().
These are not exactly the same; COALESCE can do more than ISNULL()
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now