SQL SUM giving unexpected results

Posted on 2012-08-30
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

    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
    That's the jobbie!

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now