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?
LVL 1
JElsterAsked:
Who is Participating?
 
PortletPaulConnect With a Mentor freelancerCommented:
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
 
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Mike EghtebasConnect With a Mentor Database and Application DeveloperCommented:
That should be handled in SELECT portion:

Select Sum(IsNull(Amount,0))
0
 
HuaMin ChenConnect With a Mentor System 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.