SUM returning NULL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SUM returning NULL

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-10-26 : 11:54:30

I have the following:

SELECT lg.Name, CONVERT(VARCHAR, SUM(cd.CaseTimeElapsedMinutes)) AS totalTime,
COUNT(DISTINCT ch.CaseID) AS numOfCases,
lg.locationGroupID, AVG(DATEDIFF(DAY, ch.CreatedDateTime, ch.Deadline)) AS avgTime,
SUM(CASE WHEN cs.OpenFlag = 1 then 1 else 0 end) AS numOpenCases

FROM CaseHeader AS ch
LEFT OUTER JOIN CaseDetail cd ON ch.CaseID = cd.CaseDetailID
INNER JOIN CaseStatus cs ON ch.CaseStatusID = cs.CaseStatusID
INNER JOIN Location l ON ch.LocationID = l.LocationID
INNER JOIN locationGroup lg ON l.PrimaryLocationGroupID = lg.locationGroupID

GROUP BY lg.Name, lg.locationGroupID

It returns 3 rows and all data columns are ok except totalTime.

This line: CONVERT(VARCHAR, SUM(cd.CaseTimeElapsedMinutes)) AS totalTime is not working right for some reason.

Does it have to do with the Left Outer join for that table?

I get back for each row: 0, NULL, NULL

And every int in that integer field is either 0 or over.

Thanks,

Zath

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-26 : 11:58:11

why are you converting to a VARCHAR? Just calculate and return the value with SUM. If you need to round it or not output decimals, do that at your presentation layer (report, web page, etc), convert it an integer, or use the ROUND() function in T-SQL ... Don't return numeric data as strings from SQL....

- Jeff
http://weblogs.sqlteam.com/JeffS

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-10-26 : 12:22:25

Converting to varchar or not does NOT matter, I've tried both.

So why the null values???

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-26 : 13:38:41

If you ever have a calculation that appears to be not working, always be sure to confirm at the underlying values used by that calculation; Look at the data without the GROUP BY and the SUM, just examine the raw values to ensure that there is data there. Perhaps your join condition is not returning any matching rows?

- Jeff
http://weblogs.sqlteam.com/JeffS

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-10-26 : 13:39:44

You are doing a LEFT OUTER JOIN so it is possible that CaseDetail does not have data for that query?

Here is a sample that shows the issue:

DECLARE @Foo TABLE (ID INT, Val INT)
DECLARE @Bar TABLE (ID INT, Total INT)

INSERT @Foo
SELECT 1, 9
UNION ALL SELECT 1, 10
UNION ALL SELECT 2, 4
UNION ALL SELECT 3, 4

INSERT @Bar
SELECT 1, 99
UNION ALL SELECT 3, 109

SELECT
	Foo.ID,
	SUM(Bar.Total)
FROM
	@Foo AS Foo
LEFT OUTER JOIN
	@Bar AS Bar
	ON Foo.ID = Bar.ID
GROUP BY
	Foo.ID

EDIT: Added Sample.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-28 : 15:22:34

Make use if ISNULL or COALESCE function to convert NULL values to a default of your choice.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources