Null value eliminated from aggregate??? 
Author Message
 Null value eliminated from aggregate???

Hi -

I have a SQL Statement (below) that seems to be working ok (thanks to some
help from the good folks in this newsgroup) but I keep getting some extra
stuff when I run the query in SQL Query Analyzer. It says "Warning: Null
value eliminated from aggregate."

What is going on here? Should I be concerned about this?

Thanks,

Paul Hastings
Principal Computer Scientist
Jorge Scientific Corporation
104 Park Drive
Warner Robins, GA 31088
Voice: (478) 923-2662
FAX: (478) 923-5625

- - - SQL Statement - - -

SELECT COUNT(*) L0,

COUNT (case when (LEN(LTRIM(HAE.HAE2_Serial_Number))=0 or
HAE.HAE2_Serial_Number is null) then 1  end)-COUNT(case when
(LEN(LTRIM(HAE.HAE1_Serial_Number))=0 or HAE.HAE1_Serial_Number is null)
then 1  end) L1,

COUNT(case when (LEN(LTRIM(HAE.HAE3_Serial_Number))=0 or
HAE.HAE3_Serial_Number is null) then 1 end)-COUNT(case when
(LEN(LTRIM(HAE.HAE2_Serial_Number))=0 or HAE.HAE2_Serial_Number is null)
then 1 end) L2,

COUNT(case when (LEN(LTRIM(HAE.HAE4_Serial_Number))=0 or
HAE.HAE4_Serial_Number is null) then 1 end)-COUNT(case when
(LEN(LTRIM(HAE.HAE3_Serial_Number))=0 or HAE.HAE3_Serial_Number is null)
then 1 end) L3,

COUNT(case when (LEN(LTRIM(HAE.HAE5_Serial_Number))=0 or
HAE.HAE5_Serial_Number is null) then 1 end)-COUNT(case when
(LEN(LTRIM(HAE.HAE4_Serial_Number))=0 or HAE.HAE4_Serial_Number is null)
then 1 end) L4,

COUNT(case when (LEN(LTRIM(HAE5_Serial_Number)) > 0) then 1 end) L5

FROM HAE INNER JOIN SERIAL_NUMBER ON HAE.SERIAL_NUMBER =
SERIAL_NUMBER.SERIAL_NUMBER
WHERE (SERIAL_NUMBER.SHIP_STATUS_CODE = '1') AND (SERIAL_NUMBER.ACCOUNT_ID =
'KLIF001')

- - - End of Statement - - -

- - - Result - - -
L0          L1          L2          L3          L4          L5
----------- ----------- ----------- ----------- ----------- -----------
86          3           3           3           2           10

(1 row(s) affected)

Warning: Null value eliminated from aggregate.

- - - End of Result - - -



Sat, 29 May 2004 05:20:55 GMT
 Null value eliminated from aggregate???

paul,

set ansi_warnings OFF

-oj


Quote:
> Hi -

> I have a SQL Statement (below) that seems to be working ok (thanks to some
> help from the good folks in this newsgroup) but I keep getting some extra
> stuff when I run the query in SQL Query Analyzer. It says "Warning: Null
> value eliminated from aggregate."

> What is going on here? Should I be concerned about this?

> Thanks,

> Paul Hastings
> Principal Computer Scientist
> Jorge Scientific Corporation
> 104 Park Drive
> Warner Robins, GA 31088
> Voice: (478) 923-2662
> FAX: (478) 923-5625

> - - - SQL Statement - - -

> SELECT COUNT(*) L0,

> COUNT (case when (LEN(LTRIM(HAE.HAE2_Serial_Number))=0 or
> HAE.HAE2_Serial_Number is null) then 1  end)-COUNT(case when
> (LEN(LTRIM(HAE.HAE1_Serial_Number))=0 or HAE.HAE1_Serial_Number is null)
> then 1  end) L1,

> COUNT(case when (LEN(LTRIM(HAE.HAE3_Serial_Number))=0 or
> HAE.HAE3_Serial_Number is null) then 1 end)-COUNT(case when
> (LEN(LTRIM(HAE.HAE2_Serial_Number))=0 or HAE.HAE2_Serial_Number is null)
> then 1 end) L2,

> COUNT(case when (LEN(LTRIM(HAE.HAE4_Serial_Number))=0 or
> HAE.HAE4_Serial_Number is null) then 1 end)-COUNT(case when
> (LEN(LTRIM(HAE.HAE3_Serial_Number))=0 or HAE.HAE3_Serial_Number is null)
> then 1 end) L3,

> COUNT(case when (LEN(LTRIM(HAE.HAE5_Serial_Number))=0 or
> HAE.HAE5_Serial_Number is null) then 1 end)-COUNT(case when
> (LEN(LTRIM(HAE.HAE4_Serial_Number))=0 or HAE.HAE4_Serial_Number is null)
> then 1 end) L4,

> COUNT(case when (LEN(LTRIM(HAE5_Serial_Number)) > 0) then 1 end) L5

> FROM HAE INNER JOIN SERIAL_NUMBER ON HAE.SERIAL_NUMBER =
> SERIAL_NUMBER.SERIAL_NUMBER
> WHERE (SERIAL_NUMBER.SHIP_STATUS_CODE = '1') AND (SERIAL_NUMBER.ACCOUNT_ID
=
> 'KLIF001')

> - - - End of Statement - - -

> - - - Result - - -
> L0          L1          L2          L3          L4          L5
> ----------- ----------- ----------- ----------- ----------- -----------
> 86          3           3           3           2           10

> (1 row(s) affected)

> Warning: Null value eliminated from aggregate.

> - - - End of Result - - -



Sat, 29 May 2004 05:30:31 GMT
 Null value eliminated from aggregate???
Concerned? Depends on what you want to happen to nulls. Nulls are simply
excluded from your aggregates by default. That means if you are taking an
average of 10, 20 and null, by default you'll get 15. If you need to get 10,
you need to convert the null to 0

select avg(coalesce(field, 0)) from ...

Zach


Quote:
> Hi -

> I have a SQL Statement (below) that seems to be working ok (thanks to some
> help from the good folks in this newsgroup) but I keep getting some extra
> stuff when I run the query in SQL Query Analyzer. It says "Warning: Null
> value eliminated from aggregate."

> What is going on here? Should I be concerned about this?

> Thanks,

> Paul Hastings
> Principal Computer Scientist
> Jorge Scientific Corporation
> 104 Park Drive
> Warner Robins, GA 31088
> Voice: (478) 923-2662
> FAX: (478) 923-5625

> - - - SQL Statement - - -

> SELECT COUNT(*) L0,

> COUNT (case when (LEN(LTRIM(HAE.HAE2_Serial_Number))=0 or
> HAE.HAE2_Serial_Number is null) then 1  end)-COUNT(case when
> (LEN(LTRIM(HAE.HAE1_Serial_Number))=0 or HAE.HAE1_Serial_Number is null)
> then 1  end) L1,

> COUNT(case when (LEN(LTRIM(HAE.HAE3_Serial_Number))=0 or
> HAE.HAE3_Serial_Number is null) then 1 end)-COUNT(case when
> (LEN(LTRIM(HAE.HAE2_Serial_Number))=0 or HAE.HAE2_Serial_Number is null)
> then 1 end) L2,

> COUNT(case when (LEN(LTRIM(HAE.HAE4_Serial_Number))=0 or
> HAE.HAE4_Serial_Number is null) then 1 end)-COUNT(case when
> (LEN(LTRIM(HAE.HAE3_Serial_Number))=0 or HAE.HAE3_Serial_Number is null)
> then 1 end) L3,

> COUNT(case when (LEN(LTRIM(HAE.HAE5_Serial_Number))=0 or
> HAE.HAE5_Serial_Number is null) then 1 end)-COUNT(case when
> (LEN(LTRIM(HAE.HAE4_Serial_Number))=0 or HAE.HAE4_Serial_Number is null)
> then 1 end) L4,

> COUNT(case when (LEN(LTRIM(HAE5_Serial_Number)) > 0) then 1 end) L5

> FROM HAE INNER JOIN SERIAL_NUMBER ON HAE.SERIAL_NUMBER =
> SERIAL_NUMBER.SERIAL_NUMBER
> WHERE (SERIAL_NUMBER.SHIP_STATUS_CODE = '1') AND (SERIAL_NUMBER.ACCOUNT_ID
=
> 'KLIF001')

> - - - End of Statement - - -

> - - - Result - - -
> L0          L1          L2          L3          L4          L5
> ----------- ----------- ----------- ----------- ----------- -----------
> 86          3           3           3           2           10

> (1 row(s) affected)

> Warning: Null value eliminated from aggregate.

> - - - End of Result - - -



Sat, 29 May 2004 05:43:21 GMT
 Null value eliminated from aggregate???

Quote:
>> It says "Warning: Null value eliminated from aggregate."  What is going on here? Should I be concerned about this? <<

Read the specifications for the language.  All aggregate functions (actually called set functions in the ANSI/ISO Standards) drop NULLs before doing calculations.  This warning tells you that the grouped table dropped NULLs.  This warning is not fatal, but it is important.  

For example, say you are grouping total sales by sales team.  But if one of the salesmen has a NULL to represent that they have failed to report their sles yet, you want to disregard his group's total.  

The bad news is with CURSORs.  When does this message appear?  Well, that is implementation defined.  It can appear when you execute the DECLARE CURSOR, OPEN CURSOR, or FETCH statements.  Your program logic will be very different in each of these situations.  

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, datatypes, etc. in your schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Sat, 29 May 2004 06:38:51 GMT
 Null value eliminated from aggregate???
Paul,

create table Paul (c1 int NULL)
go

-- counting only non-NULL values
insert into Paul values (1)
insert into Paul values (2)
select count(c1) from Paul

-- counting with NULL values
insert into Paul values (NULL)
select count(c1) from Paul

-- equivalent to counting with NULL values, but
-- without the warning message
select sum(case when c1 is null then 0 else 1 end)
from Paul

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:
> Hi -

> I have a SQL Statement (below) that seems to be working ok (thanks to some
> help from the good folks in this newsgroup) but I keep getting some extra
> stuff when I run the query in SQL Query Analyzer. It says "Warning: Null
> value eliminated from aggregate."

> What is going on here? Should I be concerned about this?

> Thanks,

> Paul Hastings
> Principal Computer Scientist
> Jorge Scientific Corporation
> 104 Park Drive
> Warner Robins, GA 31088
> Voice: (478) 923-2662
> FAX: (478) 923-5625

> - - - SQL Statement - - -

> SELECT COUNT(*) L0,

> COUNT (case when (LEN(LTRIM(HAE.HAE2_Serial_Number))=0 or
> HAE.HAE2_Serial_Number is null) then 1  end)-COUNT(case when
> (LEN(LTRIM(HAE.HAE1_Serial_Number))=0 or HAE.HAE1_Serial_Number is null)
> then 1  end) L1,

> COUNT(case when (LEN(LTRIM(HAE.HAE3_Serial_Number))=0 or
> HAE.HAE3_Serial_Number is null) then 1 end)-COUNT(case when
> (LEN(LTRIM(HAE.HAE2_Serial_Number))=0 or HAE.HAE2_Serial_Number is null)
> then 1 end) L2,

> COUNT(case when (LEN(LTRIM(HAE.HAE4_Serial_Number))=0 or
> HAE.HAE4_Serial_Number is null) then 1 end)-COUNT(case when
> (LEN(LTRIM(HAE.HAE3_Serial_Number))=0 or HAE.HAE3_Serial_Number is null)
> then 1 end) L3,

> COUNT(case when (LEN(LTRIM(HAE.HAE5_Serial_Number))=0 or
> HAE.HAE5_Serial_Number is null) then 1 end)-COUNT(case when
> (LEN(LTRIM(HAE.HAE4_Serial_Number))=0 or HAE.HAE4_Serial_Number is null)
> then 1 end) L4,

> COUNT(case when (LEN(LTRIM(HAE5_Serial_Number)) > 0) then 1 end) L5

> FROM HAE INNER JOIN SERIAL_NUMBER ON HAE.SERIAL_NUMBER =
> SERIAL_NUMBER.SERIAL_NUMBER
> WHERE (SERIAL_NUMBER.SHIP_STATUS_CODE = '1') AND (SERIAL_NUMBER.ACCOUNT_ID
=
> 'KLIF001')

> - - - End of Statement - - -

> - - - Result - - -
> L0          L1          L2          L3          L4          L5
> ----------- ----------- ----------- ----------- ----------- -----------
> 86          3           3           3           2           10

> (1 row(s) affected)

> Warning: Null value eliminated from aggregate.

> - - - End of Result - - -



Sat, 29 May 2004 11:09:06 GMT
 Null value eliminated from aggregate???
oj,

Awww, c'mon on   :-)

You really don't want to be suggesting turning off ANSI settings. Think of
the limitations that does in case of indexed views   :-)

BPM


Quote:
> paul,

> set ansi_warnings OFF

> -oj



> > Hi -

> > I have a SQL Statement (below) that seems to be working ok (thanks to
some
> > help from the good folks in this newsgroup) but I keep getting some
extra
> > stuff when I run the query in SQL Query Analyzer. It says "Warning: Null
> > value eliminated from aggregate."

> > What is going on here? Should I be concerned about this?

> > Thanks,

> > Paul Hastings
> > Principal Computer Scientist
> > Jorge Scientific Corporation
> > 104 Park Drive
> > Warner Robins, GA 31088
> > Voice: (478) 923-2662
> > FAX: (478) 923-5625

> > - - - SQL Statement - - -

> > SELECT COUNT(*) L0,

> > COUNT (case when (LEN(LTRIM(HAE.HAE2_Serial_Number))=0 or
> > HAE.HAE2_Serial_Number is null) then 1  end)-COUNT(case when
> > (LEN(LTRIM(HAE.HAE1_Serial_Number))=0 or HAE.HAE1_Serial_Number is null)
> > then 1  end) L1,

> > COUNT(case when (LEN(LTRIM(HAE.HAE3_Serial_Number))=0 or
> > HAE.HAE3_Serial_Number is null) then 1 end)-COUNT(case when
> > (LEN(LTRIM(HAE.HAE2_Serial_Number))=0 or HAE.HAE2_Serial_Number is null)
> > then 1 end) L2,

> > COUNT(case when (LEN(LTRIM(HAE.HAE4_Serial_Number))=0 or
> > HAE.HAE4_Serial_Number is null) then 1 end)-COUNT(case when
> > (LEN(LTRIM(HAE.HAE3_Serial_Number))=0 or HAE.HAE3_Serial_Number is null)
> > then 1 end) L3,

> > COUNT(case when (LEN(LTRIM(HAE.HAE5_Serial_Number))=0 or
> > HAE.HAE5_Serial_Number is null) then 1 end)-COUNT(case when
> > (LEN(LTRIM(HAE.HAE4_Serial_Number))=0 or HAE.HAE4_Serial_Number is null)
> > then 1 end) L4,

> > COUNT(case when (LEN(LTRIM(HAE5_Serial_Number)) > 0) then 1 end) L5

> > FROM HAE INNER JOIN SERIAL_NUMBER ON HAE.SERIAL_NUMBER =
> > SERIAL_NUMBER.SERIAL_NUMBER
> > WHERE (SERIAL_NUMBER.SHIP_STATUS_CODE = '1') AND

(SERIAL_NUMBER.ACCOUNT_ID

- Show quoted text -

Quote:
> =
> > 'KLIF001')

> > - - - End of Statement - - -

> > - - - Result - - -
> > L0          L1          L2          L3          L4          L5
> > ----------- ----------- ----------- ----------- ----------- -----------
> > 86          3           3           3           2           10

> > (1 row(s) affected)

> > Warning: Null value eliminated from aggregate.

> > - - - End of Result - - -



Sat, 29 May 2004 11:12:43 GMT
 Null value eliminated from aggregate???
hehhehe...then we have to change the code at that time...:)

-oj


Quote:
> oj,

> Awww, c'mon on   :-)

> You really don't want to be suggesting turning off ANSI settings. Think of
> the limitations that does in case of indexed views   :-)

> BPM



> > paul,

> > set ansi_warnings OFF

> > -oj



> > > Hi -

> > > I have a SQL Statement (below) that seems to be working ok (thanks to
> some
> > > help from the good folks in this newsgroup) but I keep getting some
> extra
> > > stuff when I run the query in SQL Query Analyzer. It says "Warning:
Null
> > > value eliminated from aggregate."

> > > What is going on here? Should I be concerned about this?

> > > Thanks,

> > > Paul Hastings
> > > Principal Computer Scientist
> > > Jorge Scientific Corporation
> > > 104 Park Drive
> > > Warner Robins, GA 31088
> > > Voice: (478) 923-2662
> > > FAX: (478) 923-5625

> > > - - - SQL Statement - - -

> > > SELECT COUNT(*) L0,

> > > COUNT (case when (LEN(LTRIM(HAE.HAE2_Serial_Number))=0 or
> > > HAE.HAE2_Serial_Number is null) then 1  end)-COUNT(case when
> > > (LEN(LTRIM(HAE.HAE1_Serial_Number))=0 or HAE.HAE1_Serial_Number is
null)
> > > then 1  end) L1,

> > > COUNT(case when (LEN(LTRIM(HAE.HAE3_Serial_Number))=0 or
> > > HAE.HAE3_Serial_Number is null) then 1 end)-COUNT(case when
> > > (LEN(LTRIM(HAE.HAE2_Serial_Number))=0 or HAE.HAE2_Serial_Number is
null)
> > > then 1 end) L2,

> > > COUNT(case when (LEN(LTRIM(HAE.HAE4_Serial_Number))=0 or
> > > HAE.HAE4_Serial_Number is null) then 1 end)-COUNT(case when
> > > (LEN(LTRIM(HAE.HAE3_Serial_Number))=0 or HAE.HAE3_Serial_Number is
null)
> > > then 1 end) L3,

> > > COUNT(case when (LEN(LTRIM(HAE.HAE5_Serial_Number))=0 or
> > > HAE.HAE5_Serial_Number is null) then 1 end)-COUNT(case when
> > > (LEN(LTRIM(HAE.HAE4_Serial_Number))=0 or HAE.HAE4_Serial_Number is
null)
> > > then 1 end) L4,

> > > COUNT(case when (LEN(LTRIM(HAE5_Serial_Number)) > 0) then 1 end) L5

> > > FROM HAE INNER JOIN SERIAL_NUMBER ON HAE.SERIAL_NUMBER =
> > > SERIAL_NUMBER.SERIAL_NUMBER
> > > WHERE (SERIAL_NUMBER.SHIP_STATUS_CODE = '1') AND
> (SERIAL_NUMBER.ACCOUNT_ID
> > =
> > > 'KLIF001')

> > > - - - End of Statement - - -

> > > - - - Result - - -
> > > L0          L1          L2          L3          L4          L5

> ----------- ----------- ----------- ----------- ----------- -----------
> > > 86          3           3           3           2           10

> > > (1 row(s) affected)

> > > Warning: Null value eliminated from aggregate.

> > > - - - End of Result - - -



Sat, 29 May 2004 11:39:43 GMT
 Null value eliminated from aggregate???
oj,

Let me guess ... you work as a consultant    :-)

BPM


Quote:
> hehhehe...then we have to change the code at that time...:)

> -oj



> > oj,

> > Awww, c'mon on   :-)

> > You really don't want to be suggesting turning off ANSI settings. Think
of
> > the limitations that does in case of indexed views   :-)

> > BPM



> > > paul,

> > > set ansi_warnings OFF

> > > -oj



> > > > Hi -

> > > > I have a SQL Statement (below) that seems to be working ok (thanks
to
> > some
> > > > help from the good folks in this newsgroup) but I keep getting some
> > extra
> > > > stuff when I run the query in SQL Query Analyzer. It says "Warning:
> Null
> > > > value eliminated from aggregate."

> > > > What is going on here? Should I be concerned about this?

> > > > Thanks,

> > > > Paul Hastings
> > > > Principal Computer Scientist
> > > > Jorge Scientific Corporation
> > > > 104 Park Drive
> > > > Warner Robins, GA 31088
> > > > Voice: (478) 923-2662
> > > > FAX: (478) 923-5625

> > > > - - - SQL Statement - - -

> > > > SELECT COUNT(*) L0,

> > > > COUNT (case when (LEN(LTRIM(HAE.HAE2_Serial_Number))=0 or
> > > > HAE.HAE2_Serial_Number is null) then 1  end)-COUNT(case when
> > > > (LEN(LTRIM(HAE.HAE1_Serial_Number))=0 or HAE.HAE1_Serial_Number is
> null)
> > > > then 1  end) L1,

> > > > COUNT(case when (LEN(LTRIM(HAE.HAE3_Serial_Number))=0 or
> > > > HAE.HAE3_Serial_Number is null) then 1 end)-COUNT(case when
> > > > (LEN(LTRIM(HAE.HAE2_Serial_Number))=0 or HAE.HAE2_Serial_Number is
> null)
> > > > then 1 end) L2,

> > > > COUNT(case when (LEN(LTRIM(HAE.HAE4_Serial_Number))=0 or
> > > > HAE.HAE4_Serial_Number is null) then 1 end)-COUNT(case when
> > > > (LEN(LTRIM(HAE.HAE3_Serial_Number))=0 or HAE.HAE3_Serial_Number is
> null)
> > > > then 1 end) L3,

> > > > COUNT(case when (LEN(LTRIM(HAE.HAE5_Serial_Number))=0 or
> > > > HAE.HAE5_Serial_Number is null) then 1 end)-COUNT(case when
> > > > (LEN(LTRIM(HAE.HAE4_Serial_Number))=0 or HAE.HAE4_Serial_Number is
> null)
> > > > then 1 end) L4,

> > > > COUNT(case when (LEN(LTRIM(HAE5_Serial_Number)) > 0) then 1 end) L5

> > > > FROM HAE INNER JOIN SERIAL_NUMBER ON HAE.SERIAL_NUMBER =
> > > > SERIAL_NUMBER.SERIAL_NUMBER
> > > > WHERE (SERIAL_NUMBER.SHIP_STATUS_CODE = '1') AND
> > (SERIAL_NUMBER.ACCOUNT_ID
> > > =
> > > > 'KLIF001')

> > > > - - - End of Statement - - -

> > > > - - - Result - - -
> > > > L0          L1          L2          L3          L4          L5

> > ----------- ----------- ----------- ----------- ----------- -----------
> > > > 86          3           3           3           2           10

> > > > (1 row(s) affected)

> > > > Warning: Null value eliminated from aggregate.

> > > > - - - End of Result - - -



Sat, 29 May 2004 11:50:09 GMT
 Null value eliminated from aggregate???
hehhehe...

-oj


Quote:
> oj,

> Let me guess ... you work as a consultant    :-)

> BPM



> > hehhehe...then we have to change the code at that time...:)

> > -oj



> > > oj,

> > > Awww, c'mon on   :-)

> > > You really don't want to be suggesting turning off ANSI settings.
Think
> of
> > > the limitations that does in case of indexed views   :-)

> > > BPM



> > > > paul,

> > > > set ansi_warnings OFF

> > > > -oj



> > > > > Hi -

> > > > > I have a SQL Statement (below) that seems to be working ok (thanks
> to
> > > some
> > > > > help from the good folks in this newsgroup) but I keep getting
some
> > > extra
> > > > > stuff when I run the query in SQL Query Analyzer. It says
"Warning:
> > Null
> > > > > value eliminated from aggregate."

> > > > > What is going on here? Should I be concerned about this?

> > > > > Thanks,

> > > > > Paul Hastings
> > > > > Principal Computer Scientist
> > > > > Jorge Scientific Corporation
> > > > > 104 Park Drive
> > > > > Warner Robins, GA 31088
> > > > > Voice: (478) 923-2662
> > > > > FAX: (478) 923-5625

> > > > > - - - SQL Statement - - -

> > > > > SELECT COUNT(*) L0,

> > > > > COUNT (case when (LEN(LTRIM(HAE.HAE2_Serial_Number))=0 or
> > > > > HAE.HAE2_Serial_Number is null) then 1  end)-COUNT(case when
> > > > > (LEN(LTRIM(HAE.HAE1_Serial_Number))=0 or HAE.HAE1_Serial_Number is
> > null)
> > > > > then 1  end) L1,

> > > > > COUNT(case when (LEN(LTRIM(HAE.HAE3_Serial_Number))=0 or
> > > > > HAE.HAE3_Serial_Number is null) then 1 end)-COUNT(case when
> > > > > (LEN(LTRIM(HAE.HAE2_Serial_Number))=0 or HAE.HAE2_Serial_Number is
> > null)
> > > > > then 1 end) L2,

> > > > > COUNT(case when (LEN(LTRIM(HAE.HAE4_Serial_Number))=0 or
> > > > > HAE.HAE4_Serial_Number is null) then 1 end)-COUNT(case when
> > > > > (LEN(LTRIM(HAE.HAE3_Serial_Number))=0 or HAE.HAE3_Serial_Number is
> > null)
> > > > > then 1 end) L3,

> > > > > COUNT(case when (LEN(LTRIM(HAE.HAE5_Serial_Number))=0 or
> > > > > HAE.HAE5_Serial_Number is null) then 1 end)-COUNT(case when
> > > > > (LEN(LTRIM(HAE.HAE4_Serial_Number))=0 or HAE.HAE4_Serial_Number is
> > null)
> > > > > then 1 end) L4,

> > > > > COUNT(case when (LEN(LTRIM(HAE5_Serial_Number)) > 0) then 1 end)
L5

> > > > > FROM HAE INNER JOIN SERIAL_NUMBER ON HAE.SERIAL_NUMBER =
> > > > > SERIAL_NUMBER.SERIAL_NUMBER
> > > > > WHERE (SERIAL_NUMBER.SHIP_STATUS_CODE = '1') AND
> > > (SERIAL_NUMBER.ACCOUNT_ID
> > > > =
> > > > > 'KLIF001')

> > > > > - - - End of Statement - - -

> > > > > - - - Result - - -
> > > > > L0          L1          L2          L3          L4          L5

> ----------- ----------- ----------- ----------- ----------- -----------
> > > > > 86          3           3           3           2           10

> > > > > (1 row(s) affected)

> > > > > Warning: Null value eliminated from aggregate.

> > > > > - - - End of Result - - -



Sat, 29 May 2004 11:56:21 GMT
 Null value eliminated from aggregate???
In addition to the other posts:

If you want to exclude the NULL from the average, and still not get that message, you can
include a restriction in the WHERE clause:

SELECT SUM(price) FROM titles
WHERE price IS NOT NULL

Above can be problematic in more complicated cases, where you need the NULLs for other stuff...

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...


Quote:
> Hi -

> I have a SQL Statement (below) that seems to be working ok (thanks to some
> help from the good folks in this newsgroup) but I keep getting some extra
> stuff when I run the query in SQL Query Analyzer. It says "Warning: Null
> value eliminated from aggregate."

> What is going on here? Should I be concerned about this?

> Thanks,

> Paul Hastings
> Principal Computer Scientist
> Jorge Scientific Corporation
> 104 Park Drive
> Warner Robins, GA 31088
> Voice: (478) 923-2662
> FAX: (478) 923-5625

> - - - SQL Statement - - -

> SELECT COUNT(*) L0,

> COUNT (case when (LEN(LTRIM(HAE.HAE2_Serial_Number))=0 or
> HAE.HAE2_Serial_Number is null) then 1  end)-COUNT(case when
> (LEN(LTRIM(HAE.HAE1_Serial_Number))=0 or HAE.HAE1_Serial_Number is null)
> then 1  end) L1,

> COUNT(case when (LEN(LTRIM(HAE.HAE3_Serial_Number))=0 or
> HAE.HAE3_Serial_Number is null) then 1 end)-COUNT(case when
> (LEN(LTRIM(HAE.HAE2_Serial_Number))=0 or HAE.HAE2_Serial_Number is null)
> then 1 end) L2,

> COUNT(case when (LEN(LTRIM(HAE.HAE4_Serial_Number))=0 or
> HAE.HAE4_Serial_Number is null) then 1 end)-COUNT(case when
> (LEN(LTRIM(HAE.HAE3_Serial_Number))=0 or HAE.HAE3_Serial_Number is null)
> then 1 end) L3,

> COUNT(case when (LEN(LTRIM(HAE.HAE5_Serial_Number))=0 or
> HAE.HAE5_Serial_Number is null) then 1 end)-COUNT(case when
> (LEN(LTRIM(HAE.HAE4_Serial_Number))=0 or HAE.HAE4_Serial_Number is null)
> then 1 end) L4,

> COUNT(case when (LEN(LTRIM(HAE5_Serial_Number)) > 0) then 1 end) L5

> FROM HAE INNER JOIN SERIAL_NUMBER ON HAE.SERIAL_NUMBER =
> SERIAL_NUMBER.SERIAL_NUMBER
> WHERE (SERIAL_NUMBER.SHIP_STATUS_CODE = '1') AND (SERIAL_NUMBER.ACCOUNT_ID =
> 'KLIF001')

> - - - End of Statement - - -

> - - - Result - - -
> L0          L1          L2          L3          L4          L5
> ----------- ----------- ----------- ----------- ----------- -----------
> 86          3           3           3           2           10

> (1 row(s) affected)

> Warning: Null value eliminated from aggregate.

> - - - End of Result - - -



Sat, 29 May 2004 16:01:19 GMT
 
 [ 10 post ] 

 Relevant Pages 

1. Warning: Null value eliminated from aggregate.

2. 8153: Warning, null value eliminated from aggregate.

3. Null value eliminated from aggregate

4. Warning: Null value eliminated from aggregate

5. 8153: Warning, null value eliminated from aggregate.

6. Warning, null value eliminated from aggregate.

7. Warning, null value eliminated from aggregate

8. 01003: Warning, null value eliminated from aggregate

9. Null value eliminated from aggregate

10. Error 8153 -- Warning: Null value eliminated from aggregate

11. ERROR: Null value eliminated from aggregate

12. 01003 Error (Null value eliminated from aggregate.)


 
Powered by phpBB® Forum Software