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 <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>>
*Date: *November 9, 2005 7:12:45 AM PST
*To: [EMAIL PROTECTED] <mailto:[email protected]>
*Subject: **COUNT queries*
*Reply-To: [EMAIL PROTECTED] <mailto:[email protected]>
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 [EMAIL PROTECTED] Engineering GmbH
mailto:[EMAIL PROTECTED] 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:[EMAIL PROTECTED]
P.S. A good JDO? O, Gasp!
