db-jdo-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Fwd: COUNT queries
Date Wed, 09 Nov 2005 19:25:00 GMT
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!
>
>


Mime
View raw message