September 23, 2009 at 10:30 am
I have the following:
SELECT [Date], uShiftID, Case_Code, SUM(coalesce(Case_Count,0)) AS Case_Count, SUM(Case_Sched) AS Case_Sched, Product
FROM dbo.vw_Daily_CaseCounts AS dcc
WHERE dcc.[date]>='9/23/2009'
GROUP BY [date], uShiftID, Case_Code, Product
The data is still returning NULL when Case_Count is NULL and I thought this would return 0? Is there some syntax error that I am missing?
Thanks
September 23, 2009 at 10:49 am
Actually, I got it. The query in my post is a sub-select statement. I put the coalesce in the main select and it works.
September 23, 2009 at 10:56 am
SUM(coalesce(Case_Count,0)) ...should return 0 when Case_Count IS NULL. I would try it on a narrow result set without the other aggregate and without the other attributes.
September 23, 2009 at 2:56 pm
SSSolice (9/23/2009)
SUM(coalesce(Case_Count,0))...should return 0 when Case_Count IS NULL. I would try it on a narrow result set without the other aggregate and without the other attributes.
This is true so long as there is a row that matches his criteria. If you're doing the sum of a nullable column and you have joins/criteria etc. that could leave you in a situation of having no rows to sum, you need to wrap the sum in a coalesce as well.
IE. COALESCE(SUM(COALESCE(Case_Count,0)),0)
The inner coalesce handles any individual case_counts that are null, the outer one handles not having any case_counts to sum in the first place.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply