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

Question

I working on SQL server 2012 I face issue I can't solve this warning

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

so How to solve this warning and not display again

my query as below :

UPDATE FFFF
SET
Conflictflag= IIF((NotNULL+NuLLCount)<>RowsCount AND Ex.MaskExceptionID IS NULL ,CONCAT(Conflictflag,'ComplianceID','|'),Conflictflag),
NULLflag=IIF((NotNULL+NuLLCount)=RowsCount AND NuLLCount>0 AND NULEX.NULLExceptionID IS NULL,CONCAT(NULLflag,'ComplianceID','|'),NULLflag)
FROM
(
SELECT Masked_ID,SUM(CNT)/COUNT(Compliance_Status_ID) AS NotNULL ,SUM(NULLCount)AS NuLLCount
FROM (
SELECT FF.Masked_ID, LC.Compliance_Status_ID,
COUNT(DISTINCT LC.Zpart_ID) AS CNT,--COUNT(CASE WHEN DocumentID IS NOT NULL THEN 1 ELSE NULL END) AS CNT,
COUNT( CASE WHEN Compliance_Status_ID IS NULL THEN 1 ELSE NULL END )NULLCount
FROM ExtractReports.dbo.MultiMask FF
INNER JOIN Parts.Nop_Part ptt WITH(NOLOCK) ON ptt.Masked_ID=ff.Masked_ID --AND FF.Masked_ID=287578
--LEFT JOIN PCN.DocumentParts LC WITH(NOLOCK) ON ptt.PartID=LC.PartID
left JOIN DocumentCompliance.Output LC WITH(NOLOCK) ON ptt.PartID=LC.Zpart_ID
WHERE FF.ComplianceID LIKE '%|%'
GROUP BY FF.Masked_ID ,LC.Compliance_Status_ID
)DD
GROUP BY DD.Masked_ID
) DDFF
INNER JOIN ExtractReports.dbo.MultiMask FFFF ON DDFF.Masked_ID=FFFF.Masked_ID
LEFT JOIN [ConflictReport].dbo.MaskExceptions EX ON EX.MaskID=FFFF.Masked_ID AND EX.FunctionName='ComplianceID'
LEFT JOIN [ConflictReport].dbo.NULLExceptions NULEX ON NULEX.MaskID=FFFF.Masked_ID AND NULEX.FunctionName='ComplianceID'
WHERE --DDFF.SUMC <>FFFF.RowsCount AND
FFFF.ComplianceID LIKE '%|%'

so How to solve this issue please ?

Varun Setia · Answer

Hi, This is just an indication that Null values are part of calculation of aggregation. You can use this in beginning SET ANSI_WARNINGS OFF GO It won't cause any issue but you can supress it,refer to this: https://blog.sqlauthority.com/2015/02/13/sql-server-warning-null-value-is-eliminated-by-an-aggregate-or-other-set-operation/#:~:text=working%20with%20NULL.-,Warning%3A%20Null%20value%20is%20eliminated%20by%20an%20aggregate%20or%20other,Studio%20to%20see%20the%20effect.&text=As%20the%20error%20says%2C%20NULLs,ansi_warnings%20off%E2%80%9D%20before%20the%20script. Else, You can filter out values using WHERE [ column ] IS NOT NULL