CODEWITHSUNDEEP
sqlsql-server
Francis Ducharme
Francis Ducharme

Reputation: 4987

Cannot evaluate whether a SUM is null or even greater than 0

I have a simple query here:

select 
 WorkedHours =  
     (SELECT 
    
        CASE WHEN SUM(BillableSeconds) is not null
          THEN SUM(BillableSeconds)
          ELSE 0
        END 
      FROM Worklog WHERE IssueId = 188625
     )

from Worklog  where IssueId=188625

When I pass an IssueId that does match the criteria, the CASE works, but when I willingly pass an IssueId that I know will return no rows, 0 is not being returned.

I get this in SQL studio:

enter image description here

The idea here is to determine whether the sum is NULL, 0 or greater so a CAST to decimal doesn't yield an error.

What am I missing here ?

Upvotes: 0

Views: 63

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269683

I think you just want this:

select COALESCE(SUM(BillableSeconds), 0) as WorkedHours
from Worklog 
where IssueId = 188625;

This is an aggregation query that always returns one row, even if the where clause filters everything out.

In your version, the outer query returns one row for every matching row in the table. If no rows match, no row is returned.

Upvotes: 3

Related Questions