Warning: Null value is eliminated by an aggregate or other SET operation in Aqua Data Studio
You would mostly be using COUNT
to summarize over a UID. Therefore
COUNT([uid])
will produce the warning:
Warning: Null value is eliminated by an aggregate or other SET operation.
whilst being used with a left join, where the counted object does not exist.
Using COUNT(*)
in this case would also render incorrect results, as you would then be counting the total number of results (ie parents) that exist.
Using COUNT([uid])
IS a valid way of counting, and the warning is nothing more than a warning. However if you are concerned, and you want to get a true count of uids in this case then you could use:
SUM(CASE WHEN [uid] IS NULL THEN 0 ELSE 1 END) AS [new_count]
This would not add a lot of overheads to your query.
(tested mssql 2008)
Warning: Null value is eliminated by an aggregate or other SET operation
Use ISNULL
select jm.jobmst_id, ISNULL(max(tj.trgjob_order), 0) from jobmst jm
Your complete script
declare @eventname VARCHAR(64)
declare @eventid INT
set @eventname = 'event123'
set @eventid = (select tm.trgmst_id from trgmst tm where tm.trgmst_name = @eventname)
declare @trgjobmax INT
set @trgjobmax = (select max(trgjob_id) from trgjob)
declare @jobid TABLE (jobmst_id INT, trgjob_order INT)
insert into @jobid (jobmst_id, trgjob_order)
select jm.jobmst_id, ISNULL(max(tj.trgjob_order), 0) from jobmst jm
left outer join trgjob tj on tj.jobmst_id = jm.jobmst_id
inner join workgrp wg on wg.workgrp_id = jm.jobmst_owner
where wg.workgrp_name in ('group1', 'group2', 'group3')
and jm.jobmst_type = 2 and jm.jobmst_dirty <> 'X' and jm.jobmst_id
NOT IN (
select tj.jobmst_id from trgjob tj
where tj.trgmst_id = @eventid
)
group by jm.jobmst_id
order by jm.jobmst_id desc
Getting warning: Null value is eliminated by an aggregate or other SET operation
Mostly you should do nothing about it.
- It is possible to disable the warning by setting
ansi_warnings
off but this has other effects, e.g. on how division by zero is handled and can cause failures when your queries use features like indexed views, computed columns or XML methods. - In some limited cases you can rewrite the aggregate to avoid it. e.g.
COUNT(nullable_column)
can be rewritten asSUM(CASE WHEN nullable_column IS NULL THEN 0 ELSE 1 END)
but this isn't always possible to do straightforwardly without changing the semantics.
It's just an informational message required in the SQL standard. Apart from adding unwanted noise to the messages stream it has no ill effects (other than meaning that SQL Server can't just bypass reading NULL
rows, which can have an overhead but disabling the warning doesn't give better execution plans in this respect)
The reason for returning this message is that throughout most operations in SQL nulls propagate.
SELECT NULL + 3 + 7
returns NULL
(regarding NULL
as an unknown quantity this makes sense as ? + 3 + 7
is also unknown)
but
SELECT SUM(N)
FROM (VALUES (NULL),
(3),
(7)) V(N)
Returns 10
and the warning that nulls were ignored.
However these are exactly the semantics you want for typical aggregation queries. Otherwise the presence of a single NULL
would mean aggregations on that column over all rows would always end up yielding NULL
which is not very useful.
Which is the heaviest cake below? (Image Source, Creative Commons image altered (cropped and annotated) by me)
After the third cake was weighed the scales broke and so no information is available about the fourth but it was still possible to measure the circumference.
+--------+--------+---------------+
| CakeId | Weight | Circumference |
+--------+--------+---------------+
| 1 | 50 | 12.0 |
| 2 | 80 | 14.2 |
| 3 | 70 | 13.7 |
| 4 | NULL | 13.4 |
+--------+--------+---------------+
The query
SELECT MAX(Weight) AS MaxWeight,
AVG(Circumference) AS AvgCircumference
FROM Cakes
Returns
+-----------+------------------+
| MaxWeight | AvgCircumference |
+-----------+------------------+
| 80 | 13.325 |
+-----------+------------------+
even though technically it is not possible to say with certainty that 80 was the weight of the heaviest cake (as the unknown number may be larger) the results above are generally more useful than simply returning unknown.
+-----------+------------------+
| MaxWeight | AvgCircumference |
+-----------+------------------+
| ? | 13.325 |
+-----------+------------------+
So likely you want NULLs to be ignored, and the warning just alerts you to the fact that this is happening.
How to include count of NULL values in a temp table without changing the NULL data to 0?
Depending on your DBMS, you could convert null to 0.
SQL Server:
select
LoanId,
Constraints_Count = count(isnull(ConstraintId,0))
into #Test
from LoanExample
group by LoanId
Oracle:
select
LoanId,
Constraints_Count = count(nvl(ConstraintId,0))
into #Test
from LoanExample
group by LoanId
Other:
select
LoanId,
Constraints_Count = count(case when ConstraintId is null then 0 else ConstraintId end))
into #Test
from LoanExample
group by LoanId
Related Topics
Syntax Error at End of Input in Postgresql
SQL Server: Use Parameter in Create Database
What's the Difference Between Charfield and Textfield in Django
How to Retrieve the Current Value of an Oracle Sequence Without Increment It
Ssis Best Practice to Load N Tables from Source to Target Server
Why Are Foreign Keys More Used in Theory Than in Practice
The Alter Table Statement Conflicted with the Foreign Key Constraint
How to Change Db Schema to Dbo
With Check Add Constraint Followed by Check Constraint VS. Add Constraint
SQL Server for Xml Path Make Repeating Nodes
How to Transform Comma Separated Column into Multiples Rows in Db2
Function-Based Indexes in SQL Server
Cumulative Total in Ms SQL Server
Sum of Grouped Count in SQL Query
Concat All Column Values in SQL
SQL 'Like' Query Using '%' Where the Search Criteria Contains '%'
How to Get Rid of "Error 1329: No Data - Zero Rows Fetched, Selected, or Processed"