db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Craig L Russell <Craig.Russ...@Sun.COM>
Subject Re: COUNT queries
Date Wed, 09 Nov 2005 23:05:48 GMT
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!


Mime
View raw message