Coalesce with Sum

I am having a problem with syntax. I am trying to sum a column where some of the values will be null and because I want to include the rows where the column may be null I am attempting to coalesce to zero.

Below is my sample:

SELECT *

FROM dbo.Student w

LEFT JOIN dbo.StudentDailyAbsence q ON q.StudentID = w.StudentID

Group BY q.StudentID

Having

(SUM(Coalesce(q.AbsenceValue),0) = 0.00)

COALESCE(SUM(q.AbsenceValue) = 0.00,0)

I have tried using the coalesce statement a couple of ways with no resolution, pls help!!

Change to this:

COALESCE( q.AbsenceValue, 0)

|||Ok, but how does that incorporate summing the column?|||

Try something like this: (in case you need the student name from your student table)

SELECT w.StudentID, w.StudentName, SUM(Coalesce(q.AbsenceValue,0) ) AS sumAbsenceValue

FROM dbo.Student w

LEFT JOIN dbo.StudentDailyAbsence q ON q.StudentID = w.StudentID

Group BY w.StudentID, w.StudentName

But you don't need to do the coalesce: SUM and AVG will skip the NULL value in the caculation.

The follwing should return the same result:

SELECT w.StudentID, w.StudentName, SUM(q.AbsenceValue) AS sumAbsenceValue

FROM dbo.Student w

LEFT JOIN dbo.StudentDailyAbsence q ON q.StudentID = w.StudentID

Group BY w.StudentID, w.StudentName

|||

Thanks for putting me on the right track. I actually got the result I needed by modifying your first example a little.

SELECT w.StudentID, SUM(Coalesce(q.AbsenceValue,0) ) AS sumAbsenceValue

FROM dbo.Student w

LEFT JOIN dbo.StudentDailyAbsence q ON q.StudentID = w.StudentID

Group BY w.StudentID

Having SUM(Coalesce(q.AbsenceValue,0) ) = 0.00

This gets me the desired result. I still needed to compare the result of the sum so that it equalled 0.00.

Thanks for setting me straight, I was about an inch from pulling hairs .