Hi Craig,

I hope I am answering the correct question here. The behavior of aggregates is described in Part 2 of the ANSI spec, section 10.9.

1) A null column is excluded from a COUNT( colName ) aggregate. This is described in the section 10.9 under General Rules 4a. The database is supposed to raise a warning: "warning--null value eliminated in set function"

2) Unless you specify the DISTINCT keyword, the COUNT aggregate will not filter out duplicates. Each row, regardless of whether it is a duplicate, will go into the tally. This is described in the same section underGeneral Rules 4b.

For the record, Derby exhibits this ANSI behavior. To summarize:

-- the following query eliminates rows with null in column "a"

select count( a ) from foo;

-- the following query eliminates rows with null in column "a"

-- and eliminates duplicates

select count( distinct a ) from foo;

Cheers,

-Rick

Craig L Russell wrote:

Hi,

Is the behavior of COUNT(manager) (please see below) specified in SQL?

"SELECT COUNT(manager) FROM Employee"

Thanks,

Craig

Begin forwarded message:

*From: *Michael Watzek <mwa.tech@spree.de <mailto:mwa.tech@spree.de>>

*Date: *November 9, 2005 7:12:45 AM PST

*To: *jdo-dev@db.apache.org <mailto:jdo-dev@db.apache.org>

*Subject: **COUNT queries*

*Reply-To: *jdo-dev@db.apache.org <mailto:jdo-dev@db.apache.org>

Hi,

does COUNT in JDO consider NULL values? In SQL, NULL values are not considered by COUNT.

In JDO, does COUNT consider duplicates? In SQL, duplicates are considered by COUNT.

Our testdata has five employees. Four employees have the same manager. One employee does not have a manager.

What is the result of the following JDO query: "SELECT COUNT(manager) FROM Employee"?

Is the result 4?

Regards,

Michael

-- 

-------------------------------------------------------------------

Michael Watzek                  Tech@Spree Engineering GmbH

mailto:mwa.tech@spree.de        Buelowstr. 66

Tel.:  ++49/30/235 520 36       10783 Berlin - Germany

Fax.:  ++49/30/217 520 12       http://www.spree.de/

-------------------------------------------------------------------

Craig Russell

Architect, Sun Java Enterprise System http://java.sun.com/products/jdo

408 276-5638 mailto:Craig.Russell@sun.com

P.S. A good JDO? O, Gasp!

Craig Russell

Architect, Sun Java Enterprise System http://java.sun.com/products/jdo

408 276-5638 mailto:Craig.Russell@sun.com

P.S. A good JDO? O, Gasp!