Make SUM() return zero instead of null?

[swisstoni]

Hey guys,

Is there a quick and easy way within SQL to make SUM() return zero instead of null when no rows are selected?

Or can it only be done via code, checking if the value is null?

Thanks.

 

[jal]

Hey guys,

Is there a quick and easy way within SQL to make SUM() return zero instead of null when no rows are selected?

Or can it only be done via code, checking if the value is null?

Thanks.

Maybe something like this:

SELECT IIF(Count(Amt) = 0, 0, Sum(Amt)) FROM table1

 

[swisstoni]

Thanks.

I also found the NZ() function, which is a little bit easier

Thanks.

 

[gringope24]

Hello All,
I am facing similar problem like swistoni.

Generally I want to list all employees whose worktime (in hours) in specific dates is lower than planned. Query works when Employee has register some of his worktime. Unfortunately when Employee has no records within specific dates, then he is not mentioned in results of query, I see only people ho has at least on 1 hour.
I tried to use functions as presented below but unfortunately I am not receiving satisfactory results -.
Can anyone have an idea what I should improve?

SELECT tblEmployees.LastName, IIF(Nz(Sum(tblWorktime.WorkTime),0) = 0, 0 , Sum(tblWorktime.WorkTime)) AS SumaOfWorkTime
FROM tblEmployees INNER JOIN tblWorktime ON tblEmployees.IdEmployeePK = tblWorktime.EmployeeFK
WHERE  tblWorktime.Workdate >= #2021-09-01# AND tblWorktime.Workdate < #2021-09-30#
BY tblEmployees.LastName
HAVING IIF(Nz(Sum(tblWorktime.WorkTime),0) = 0, 0 , Sum(tblWorktime.WorkTime))<176;

 

[Minty]

Hi - This is a 14 year old thread !
You need a left join, not an inner join.

An INNER JOIN will only show records where they both match.
You may need to either make the Worktime a subquery or save it as a separate query to get the join to work.

 

[gringope24]

Ok, I made some step forward. I make a query which gives 0 when Employee has no records - below results.
But my further concern is how to create a query that will do fallowing thing: If there is no records for the Employee in specific range of date, then Sum Worktime is 0.
Now when I give WHERE condition in Workdate, then records where workdate is null are simply skipped.

 

[CJ_London]

perhaps

WHERE nz(tblWorktime.Workdate,#2021-09-01# ) >= #2021-09-01# AND tblWorktime.Workdate < #2021-09-30#

but this will return all null workdates so could be anytime

 

[gringope24]

Yeah, I will check It, neverthelss, I thought the most elegant way would be to use CASE NOT EXISTS, but it twisting my mind...