From: Martijn Tonies
Date: July 16 2004 6:49pm
Subject: Re: IFNULL returns NULL when it shouldn't
List-Archive: http://lists.mysql.com/mysql/169195
Message-Id: <00bc01c46b65$a942e920$c802a8c0@martijnlaptop>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

Hi Ed,

> How do I prevent IFNULL and ISNULL from returning a null?
>
> I have the following query where this is occurring,
>
> Select IFNULL(sum(qty),0)
> from inventory
> where partnumber=111
> group by partnumber;
>
> If the partnumber has never been in inventory then the sum and ifnull
> functions both return null. If the partnumber has been in inventory then
> the sum returns the correct sum but I need it to return a zero if it's
> null. It also doesn't matter if I use the ISNULL funtion or the Is Null
> operation with a IF statement.

It doesn't return NULL, it returns an empty set.

However, if you remove the GROUP BY, it will return 0 just
fine.

Why are you doing the grouping anyway?

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com