x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 4284

# Using ISNULL with GROUP BY

Why does thist first bit of code return zeros when there is no data, but the second bit returns nothing?

SELECT       ''
, COUNT([Region]) AS [Tot No of Incidents]
, ISNULL(SUM([On Site < 2 hrs]), 0) AS [On site < 2 hrs]
, ISNULL(SUM([Fixed < 3.75 hrs]), 0) AS [Fixed < 3.75 hrs]
WHERE fldPriorityUID = 42

SELECT       Region
, COUNT([Region]) AS [Tot No of Incidents]
, ISNULL(SUM([On Site < 2 hrs]), 0) AS [On site < 2 hrs]
, ISNULL(SUM([Fixed < 3.75 hrs]), 0) AS [Fixed < 3.75 hrs]
WHERE fldPriorityUID = 42
GROUP BY Region
0
looper8
• 2
• 2
• 2
• +1
1 Solution

are u sure there are records matching the second criteria

SELECT region,[Region])
WHERE fldPriorityUID = 42
GROUP BY Region
0

or did u forget to remove the WHERE  condition, cuz, there is logically no difference b/w the first and second
0

Commented:
SELECT '' will always give u a row even ur WHERE clause returns no result, '' is more like a hardcode value
SELECT [column] will not guarantee u a row because if ur WHERE clause returns no result, the [column] will be null
0

Author Commented:
aneeshattingal: I'm trying to code for times when there is no data, so I'm running both bits against an empty table.

howye: I thought that too, but the following still returns no results:

SELECT    ''
,   Region
, COUNT([Region]) AS [Tot No of Incidents]
, ISNULL(SUM([On Site < 2 hrs]), 0) AS [On site < 2 hrs]
, ISNULL(SUM([Fixed < 3.75 hrs]), 0) AS [Fixed < 3.75 hrs]
WHERE fldPriorityUID = 42
GROUP BY Region
0

Commented:
the only difference in ur query is GROUP BY
with GROUP BY it giv u NULL, without GROUO BY is giv u 0.
however i'm stil tryin to figure out why is it so
0

Senior DBACommented:
In the first query, you are asking SQL for an overall COUNT, SUM, etc..  Even if there are no rows in the table, the COUNT() is still a *relevant* result, of 0.

In the second query, you are asking SQL for a count **by region**.  This could easily produce many rows as a result, obviously: one row **per region**.  But, if there are no regions, then there is no **relevant** result, since you explicilty asked for a COUNT, SUM, etc., **only** by region.
0

Author Commented:
Thanks Scott.  Is there then a way I can force the second query to return results (of zero), even though there is no Region in the table?
0

Senior DBACommented:
Not directly really.  But could check first and if the table has no matching rows, issue a different query.  For example:

IF EXISTS(SELECT TOP 1 Region FROM tblFireLinkReportsTemp WHERE fldPriorityUID = 42)
BEGIN
-- normal query goes here
SELECT       Region
, COUNT([Region]) AS [Tot No of Incidents]
, ISNULL(SUM([On Site < 2 hrs]), 0) AS [On site < 2 hrs]
, ISNULL(SUM([Fixed < 3.75 hrs]), 0) AS [Fixed < 3.75 hrs]
WHERE fldPriorityUID = 42
GROUP BY Region
END --IF
ELSE
BEGIN
-- "special table is empty query" goes here
SELECT       ''
, 0 AS [Tot No of Incidents]
, 0 AS [On site < 2 hrs]
, 0) AS [Fixed < 3.75 hrs]
END --ELSE
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.