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

25 Aug 2005 2:11 PM

miapjp

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

25 Aug 2005 2:32 PM

Aaron Bertrand [SQL Server MVP]

Could you provide actual specs and desired results?
http://www.aspfaq.com/5006

Show quote

> 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.

25 Aug 2005 2:51 PM

miapjp

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_RuinsPerfectMonthlyAttendace] 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

25 Aug 2005 3:01 PM

Aaron Bertrand [SQL Server MVP]

> 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

25 Aug 2005 2:52 PM

Alejandro Mesa

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

Show quote

"mia***@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
>
>

25 Aug 2005 3:50 PM

Alejandro Mesa

Sorry, my previous answer makes no sense. Try not using "group by" clause,
anyway you are filtering to just one file number.

SELECT isnull(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 '2/24/2005' AND getdate()
go

AMB

Show quote

"Alejandro Mesa" wrote:

> 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
>
> "mia***@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
> >
> >

25 Aug 2005 2:55 PM

Cowboy (Gregory A. Beamer) - MVP

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!
***************************

Show quote

"mia***@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
>
>

25 Aug 2005 3:11 PM

--CELKO--

>> ..get SQL Server 2000 to return a zero (0) if no rows are returned in a sum() 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.

25 Aug 2005 3:44 PM

AK

>> 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

25 Aug 2005 3:47 PM

miapjp

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!!!!!