SQL SUM giving unexpected results

Posted on 2012-08-30
Medium Priority
Last Modified: 2012-08-30
I am getting some unexpected results when running a SUM on a SQL database. The database is used by a 3rd party program, and I've written my own program to extract some data.

The 3rd party program is a helpdesk system, and logs technician times against jobs. The technician actions are logged as either billable or non billable.

I need to add together the amount billable hours and non-billable hours for the current month on a per-technician basis.

Here's the problem. If I run two separate queries - one to get the billable hours and one to get the non-billable hours then in my program, add the results together, I get the answer I am expecting (in this instance 177.77).

SELECT SUM(timetaken) FROM dbo.ACTIONS where who='Tech1' and When >= '08/01/2012'

SELECT SUM(nonbilltime) FROM dbo.ACTIONS where who='Tech1' and When >= '08/01/2012'

Open in new window

If I try to perform the addition in my SQL statment, my answer comes back as 174.27 and I don't know how to troubleshoot the problem...

SELECT SUM(timetaken + nonbilltime) FROM dbo.ACTIONS where who='Tech1' and When >= '08/01/2012'

Open in new window

Question by:Chris Millard
LVL 39

Accepted Solution

appari earned 2000 total points
ID: 38349145
try this, may be null values causing the difference
SELECT SUM(isnull(timetaken ,0)+ isnull(nonbilltime,0)) FROM dbo.ACTIONS where who='Tech1' and When >= '08/01/2012'

Open in new window

LVL 17

Author Closing Comment

by:Chris Millard
ID: 38349159
That's the jobbie!

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question