SQL - Summing Null Values

Question

I am trying to SUM work hours for the year 2010 for employees using the following script. Some employees may have multiple lines in the table for the same year. Although a field will populate with 0 if there are no hours, the total result may indicate NULL. I am using a SQL Standard 2005. Suggestions would be appreciated.

SELECT     TOP (100) PERCENT id, year, SUM(ISNULL(Reghours_worked, 0))       AS   RegHours, SUM(ISNULL(OThours, 0)) 
  OThours, SUM(RegHours + OThours) AS TotalHours_2010
FROM         dbo.hours
WHERE     (year = '2010')
GROUP BY id, year
ORDER BY id

``

Results below:

 id Year    RegHours    OTHours Total Hours
 658261 2010    1449            0   1449
 752466 2010    1743            0   1743
 144444 2010    1750            0   1750
 652152 2010    1142            0   NULL
 926541 2010    0           0   0

SELECT TOP (100) PERCENT id, year, SUM(ISNULL(Reghours_worked, 0)) AS RegHours, SUM(ISNULL(OThours, 0)) OThours, SUM(ISNULL(RegHours,0) + ISNULL(OThours,0)) AS TotalHours_2010 FROM dbo.hours WHERE (year = '2010') GROUP BY id, year Order BY id — Lukasz Szozda
– Lukasz Szozda, Commented Sep 1, 2015 at 16:40
you don't need sum(reghours+othours) those values are ALREADY sums, and their values wouldn't be ready until after the row's generated. just reghours+othous as total_hours should do. — Marc B
– Marc B, Commented Sep 1, 2015 at 16:42

paparazzo · Accepted Answer · 2015-09-01 16:53:15Z

1

SELECT  TOP (100) PERCENT id, year, SUM(ISNULL(Reghours_worked, 0)) AS RegHours, 
                  SUM(ISNULL(OThours, 0)) AS OThours,  
                  SUM(ISNULL(Reghours_worked, 0)+ ISNULL(OThours, 0)) AS TotalHours_2010
FROM  dbo.hours
WHERE (year = '2010')
GROUP BY id, year
ORDER BY id

edited Sep 1, 2015 at 16:53

paparazzo

45.2k24 gold badges110 silver badges180 bronze badges

answered Sep 1, 2015 at 16:46

Ruchi

1,21111 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

HABO Over a year ago

Tip: Good information about COALESCE and ISNULL is at SQL Server Pro here and MSDN Blogs here.

Ruchi Over a year ago

Thank you for sharing this information, I would definitely check that out.

WinnieBear Over a year ago

Thanks for the response. Ruchi's answer fixed the issue.

Ruchi Over a year ago

Thanx WinnieBear for trying out the solution, please make it as a answer if it worked for you :)

Collectives™ on Stack Overflow

SQL - Summing Null Values

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related