Hi Rick,
That's exactly what I was trying to find out. You just saved me a few
hours beating my head against the SQL spec.
Thanks,
Craig
On Nov 9, 2005, at 11:25 AM, Rick Hillegas wrote:
> 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!
|