Re: NULL and Aggregate Functions

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 6 Feb 2007 03:51:37 -0800
Message-ID: <1170762696.994413.52310@v33g2000cwv.googlegroups.com>

On Feb 6, 12:23 am, "klabu" <klab..._at_gmail.com> wrote:
> I don't remember but....
>
> if some_column has NULL, SUM(some_column) will be NULL ?\
>
> Is this true in 8i..?

If you are uncertain, set up a test to determine the correct answer: CREATE TABLE T1 (TA VARCHAR2(10), TN NUMBER(10));

INSERT INTO T1 VALUES ('TEST',1);
INSERT INTO T1 VALUES ('TEST2',2);
INSERT INTO T1 VALUES ('TEST3',3);
INSERT INTO T1 VALUES ('TEST4',NULL);
INSERT INTO T1 VALUES ('TEST5',NULL);

SELECT

  SUM(TN),
  MIN(TN),
  MAX(TN),

  COUNT(TN)
FROM
  T1;

SUM(TN) MIN(TN) MAX(TN) COUNT(TN)
     6 1 3 3

SELECT

  SUM(TN),
  MIN(TN),
  MAX(TN),

  COUNT(TN)
FROM
  T1
WHERE
  TA='TEST4'; SUM(TN) MIN(TN) MAX(TN) COUNT(TN)
(null) (null) (null) 0

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Feb 06 2007 - 05:51:37 CST