can't return zero when no rows exist using aggregate function sum

Hi,
I'm having a heck of a time trying to get SQL Server 2000 to return a
zero (0) if no rows are returned in a sum() statement. I know in
Oracle you wrap an NVL around the whole shebang, and in Googling it
seems as if SQL Server should work this way, too. But I cannot get it
to work, I just keep getting null.
I have tried:
what I thought would work:
SELECT ISNULL(SUM(ISNULL(PointValue, 0)), 0)
FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
tbl_event_types et ON ae.[event type] = et.eventID
WHERE [File #] = '0001001047'
AND EventDate BETWEEN '2/24/2005' AND getdate()
GROUP BY [File #]
a case statement testing for null:
SELECT CASE
WHEN SUM(ISNULL(PointValue, 0)) IS NULL THEN 0
ELSE SUM(ISNULL(PointValue, 0))
END
FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
tbl_event_types et ON ae.[event type] = et.eventID
WHERE [File #] = '0001001047'
AND EventDate BETWEEN '2/24/2005' AND getdate()
GROUP BY [File #]
wrapping the whole select statement in ISNULL as a subquery:
SELECT ISNULL((SELECT SUM(ISNULL(PointValue, 0))
FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
tbl_event_types et ON ae.[event type] = et.eventID
WHERE [File #] = '0001001047'
AND EventDate BETWEEN '2/24/2005' AND getdate()), 0)
FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
tbl_event_types et ON ae.[event type] = et.eventID
WHERE [File #] = '0001001047'
AND EventDate BETWEEN '2/24/2005' AND getdate()
and then to make sure what I was getting back was null and not empty
...something:
declare
@.n_sum numeric(6, 2)
SELECT @.n_sum = ISNULL(SUM(ISNULL(PointValue, 0)), 0)
FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
tbl_event_types et ON ae.[event type] = et.eventID
WHERE [File #] = '0001001047'
AND EventDate BETWEEN '2/24/2005' AND getdate()
GROUP BY [File #]
print 'sum is: ' + convert(char(8), isnull(@.n_sum, 0))
sum is: 0.00
What the heck am I doing wrong? I tried setting ANSI_NULL off and on,
etc...but no go. I guess I could get around by using the variable and
then returning that to the VB (since this is a stored proc used to find
one value to return to the VB6 program), but that seems kind of messy.
I would appreciate any help!
TIA
PatCould you provide actual specs and desired results?
http://www.aspfaq.com/5006

> I'm having a heck of a time trying to get SQL Server 2000 to return a
> zero (0) if no rows are returned in a sum() statement.|||I need to say this - I inherited this code and database structure!!!!
CREATE TABLE [tbl_Attendance_Event] (
[EventCode] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[File #] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Event Type] [numeric](9, 0) NULL ,
[EventDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Excused] [bit] NULL ,
[EventDate] [smalldatetime] NULL ,
[EventDuration] [money] NULL ,
[PointValue] [money] NULL ,
[ConsecDayCount] [numeric](9, 0) NULL ,
[Consecutive_Days] [bit] NULL ,
[EventRemove] [bit] NULL ,
[EventRemoveDate] [datetime] NULL ,
CONSTRAINT [PK_tbl_Attendance_Event] PRIMARY KEY NONCLUSTERED
(
[EventCode]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_tbl_Attendance_Event_tbl_Event_Types
] FOREIGN KEY
(
[Event Type]
) REFERENCES [tbl_Event_Types] (
[EventID]
)
) ON [PRIMARY]
GO
CREATE TABLE [tbl_Event_Types] (
[EventID] [numeric](9, 0) IDENTITY (1, 1) NOT NULL ,
[EventDescription] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[EventPoints] [smallmoney] NULL ,
[ExcepID] [numeric](9, 0) NULL ,
[TardyLogic] [bit] NULL ,
[Minimum Minutes] [numeric](9, 0) NULL ,
[Maximum Minutes] [numeric](9, 0) NULL ,
[ExtendsYearWindow] [bit] NULL CONSTRAINT
[DF_tbl_Event_Types_ExtendsYearWindow] DEFAULT (0),
[RuinsPerfectMonthlyAttendace] [bit] NOT NULL CONSTRAINT
[DF_tbl_Event_Types_RuinsPerfectMonthlyA
ttendace] DEFAULT (0),
[LogEvent] [bit] NULL ,
[PayCodeEvent] [bit] NULL ,
[Paycode] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Consecutive_Days] [bit] NULL ,
[AddToDropOffPeriod] [bit] NULL ,
CONSTRAINT [PK_tbl_Event_Types] PRIMARY KEY CLUSTERED
(
[EventID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
There are currently no rows that match the query, but I want to get
back a zero if that is the case. In other words, no rows = 0 for my
purposes. This query is in a stored procedure that returns the number
of points an employee has, so if there are no points I'd like a zero,
to do further processing with that zero. I could mess with the VB code
to make it zero there if there's no rows in the recordset, but this is
something that I've done with Oracle and is very useful in lots of
cases. I've Googled and seen responses that the
isnull(sum(isnull(expression, value)), value) should work, so I'm
missing something here!
Thanks
Pat|||If you want to see the groups where no matching rows exist by the where
clause, then you have to use [ALL] in the group by clause.
-- this is to reproduce what is happening to you
select c1, isnull(sum(1), 0)
from (select 1) as t1(c1)
where (c1 = 2)
group by c1
-- this is what you want
select c1, isnull(sum(1), 0)
from (select 1) as t1(c1)
where (c1 = 2)
group by all c1
go
AMB
"miapjp@.gmail.com" wrote:

> Hi,
> I'm having a heck of a time trying to get SQL Server 2000 to return a
> zero (0) if no rows are returned in a sum() statement. I know in
> Oracle you wrap an NVL around the whole shebang, and in Googling it
> seems as if SQL Server should work this way, too. But I cannot get it
> to work, I just keep getting null.
> I have tried:
> what I thought would work:
>
> SELECT ISNULL(SUM(ISNULL(PointValue, 0)), 0)
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()
> GROUP BY [File #]
> a case statement testing for null:
> SELECT CASE
> WHEN SUM(ISNULL(PointValue, 0)) IS NULL THEN 0
> ELSE SUM(ISNULL(PointValue, 0))
> END
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()
> GROUP BY [File #]
>
> wrapping the whole select statement in ISNULL as a subquery:
> SELECT ISNULL((SELECT SUM(ISNULL(PointValue, 0))
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()), 0)
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()
> and then to make sure what I was getting back was null and not empty
> ....something:
> declare
> @.n_sum numeric(6, 2)
> SELECT @.n_sum = ISNULL(SUM(ISNULL(PointValue, 0)), 0)
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()
> GROUP BY [File #]
> print 'sum is: ' + convert(char(8), isnull(@.n_sum, 0))
> sum is: 0.00
> What the heck am I doing wrong? I tried setting ANSI_NULL off and on,
> etc...but no go. I guess I could get around by using the variable and
> then returning that to the VB (since this is a stored proc used to find
> one value to return to the VB6 program), but that seems kind of messy.
> I would appreciate any help!
> TIA
> Pat
>|||You are dealing with two separate questions.
1. What is the sum on each row when there are records
2. What are the number of records (only important if 0)
One possible solution:
SELECT into a temp table. If the temp table has no rows, insert a row with
0. SELECT back off the temp table for your return value.
Since you have a filter on [File #], another option is to select into a
value and then test @.@.RowCount and set the value to 0 if @.@.RowCount = 0.
SOmething like:
DECLARE @.ReturnValue decimal
SELECT @.ReturnValue = ISNULL(SUM(ISNULL(PointValue, 0)), 0)
FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
tbl_event_types et ON ae.[event type] = et.eventID
WHERE [File #] = '0001001047'
AND EventDate BETWEEN '2/24/2005' AND getdate()
GROUP BY [File #]
IF (@.@.ROWCOUNT = 0)
SET @.ReturnValue = 0
SELECT @.ReturnValue
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"miapjp@.gmail.com" wrote:

> Hi,
> I'm having a heck of a time trying to get SQL Server 2000 to return a
> zero (0) if no rows are returned in a sum() statement. I know in
> Oracle you wrap an NVL around the whole shebang, and in Googling it
> seems as if SQL Server should work this way, too. But I cannot get it
> to work, I just keep getting null.
> I have tried:
> what I thought would work:
>
> SELECT ISNULL(SUM(ISNULL(PointValue, 0)), 0)
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()
> GROUP BY [File #]
> a case statement testing for null:
> SELECT CASE
> WHEN SUM(ISNULL(PointValue, 0)) IS NULL THEN 0
> ELSE SUM(ISNULL(PointValue, 0))
> END
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()
> GROUP BY [File #]
>
> wrapping the whole select statement in ISNULL as a subquery:
> SELECT ISNULL((SELECT SUM(ISNULL(PointValue, 0))
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()), 0)
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()
> and then to make sure what I was getting back was null and not empty
> ....something:
> declare
> @.n_sum numeric(6, 2)
> SELECT @.n_sum = ISNULL(SUM(ISNULL(PointValue, 0)), 0)
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()
> GROUP BY [File #]
> print 'sum is: ' + convert(char(8), isnull(@.n_sum, 0))
> sum is: 0.00
> What the heck am I doing wrong? I tried setting ANSI_NULL off and on,
> etc...but no go. I guess I could get around by using the variable and
> then returning that to the VB (since this is a stored proc used to find
> one value to return to the VB6 program), but that seems kind of messy.
> I would appreciate any help!
> TIA
> Pat
>|||> There are currently no rows that match the query, but I want to get
> back a zero if that is the case.
Assuming that PointValue will always be positive if it *has* a value, you
can do this ugly stuff...
SELECT MAX(foo) FROM
(SELECT foo = COALESCE(SUM(PointValue),0)
FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
tbl_event_types et ON ae.[event type] = et.eventID
WHERE [File #] = '0001001047'
AND EventDate BETWEEN '20050224' -- m/dd/yyyy is a terrible format
AND getdate()
UNION SELECT foo = 0) x|||>> ..get SQL Server 2000 to return a zero (0) if no rows are returned in a s
um() statement <<
SELECT COALESCE (SUM(i), 0) FROM EmptyTable;
However, what you are actually getting back is an empty set (ab nulo,
ex nulo) and the empty set is converted into a NULL. This is important
when you use EXISTS() predicates.|||>> There are currently no rows that match the query, but I want to get
back a zero if that is the case. In other words, no rows = 0 for my
purposes.
<<
then why don't you add a dummy row to every group:
select a, sum(b) from(
select a,b from some_table
union all
select distinct a, 0 from some_table) t|||Aha!
when I have the where clause limiting the sum, I don't need the group
by (right?). The original code also had the [file #] (again, not my
code, I don't make column names with spaces!!) in the select statement
even though it wasn't ever used - I don't know, I'm trying to make this
whole application work with bad code and bad underlying database
structure. In any case, I was trying to shoehorn in code to existing
code and not seeing the group by wasn't needed.
Thanks!!!!!