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:
*Date: *November 9, 2005 7:12:45 AM PST
*Subject: **COUNT queries*
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
Tel.: ++49/30/235 520 36 10783 Berlin - Germany
-------------------------------------------------------------------
Craig Russell
P.S. A good JDO? O, Gasp!
