NULL Value in Aggregate Function

When I run a query, I get the following warning message
and I would like to know whether it is a problem or not ?
Warning: Null value is eliminated by an aggregate or other
SET operation.
Thanks
Whether or not the warning message is a problem depends on the results you
expect. Consider the example below:
CREATE TABLE #Table1(Col1 int NULL)
INSERT INTO #Table1 VALUES(10)
INSERT INTO #Table1 VALUES(NULL)
SELECT AVG(Col1) FROM #Table1
Hope this helps.
Dan Guzman
SQL Server MVP
"Mary" <anonymous@.discussions.microsoft.com> wrote in message
news:02f401c529ca$0df65ff0$a401280a@.phx.gbl...
> When I run a query, I get the following warning message
> and I would like to know whether it is a problem or not ?
> Warning: Null value is eliminated by an aggregate or other
> SET operation.
> Thanks
|||Hi,
Most aggregate functions eliminate null values in calculations; one
exception is the COUNT function. When using the COUNT function against a
column containing null values, the null values will be eliminated from the
calculation. However, if the COUNT function uses an asterisk, it will
calculate all rows regardless of null values being present.
Again depending upon the aggregate function you are using, you might have
received this warning message. You need to understand if the aggregate
function is giving the required result set. You can use ISNULL function to
convert NULL to the desired values.
Thanks
Yogish
|||Thank you the reply from both of you.
In my select statement, aggregrate function SUM(Balance)
is used.
From the query result, I find that some of them are NULL
and some are $0. I am still looking into the reason why
some of them are NULL.
From my understanding, for NULL + $0, it will be $0. And
all balance are NULL will give me a NULL result. I
believe that the query still OK.
Thanks again.

>--Original Message--
>Hi,
>Most aggregate functions eliminate null values in
calculations; one
>exception is the COUNT function. When using the COUNT
function against a
>column containing null values, the null values will be
eliminated from the
>calculation. However, if the COUNT function uses an
asterisk, it will
>calculate all rows regardless of null values being
present.
>Again depending upon the aggregate function you are
using, you might have
>received this warning message. You need to understand if
the aggregate
>function is giving the required result set. You can use
ISNULL function to
>convert NULL to the desired values.
>--
>Thanks
>Yogish
>.
>
|||> From my understanding, for NULL + $0,
No, NULL + 0 is UNKNOWN. For convenience, the SQL language designers decided to not return UNK or
NULL when you aggregate over rows where one or more have NULL in the column. They decided to skip
(ignore) the ones with NULL. And give you a reminder (this warning).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mary" <anonymous@.discussions.microsoft.com> wrote in message
news:033801c529d5$f9747920$a401280a@.phx.gbl...[vbcol=seagreen]
> Thank you the reply from both of you.
> In my select statement, aggregrate function SUM(Balance)
> is used.
> From the query result, I find that some of them are NULL
> and some are $0. I am still looking into the reason why
> some of them are NULL.
> From my understanding, for NULL + $0, it will be $0. And
> all balance are NULL will give me a NULL result. I
> believe that the query still OK.
> Thanks again.
> calculations; one
> function against a
> eliminated from the
> asterisk, it will
> present.
> using, you might have
> the aggregate
> ISNULL function to