[
https://issues.apache.org/jira/browse/DERBY-603?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mamta A. Satoor updated DERBY-603:
----------------------------------
Urgency: Low
Labels: derby_triage10_11 (was: )
> Allow aggregates to be used within the WHERE clause of a SELECT that is part
> of a HAVING clause.
> ------------------------------------------------------------------------------------------------
>
> Key: DERBY-603
> URL: https://issues.apache.org/jira/browse/DERBY-603
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Reporter: A B
> Labels: derby_triage10_11
>
> Currently, Derby never allows aggregates to be used within WHERE clauses,
> even if the WHERE clause is part of a HAVING statement and the value of the
> aggregate can be calculated.
> For example, assume I have the following tables/data:
> create table city_scores (age int, score int);
> create table natl_avg (age int not null unique, score int);
> insert into city_scores values (14, 21), (15, 27), (15, 22), (16, 24);
> insert into natl_avg values (14, 18), (15, 23), (16, 24);
> Now, if I want to retrieve "all ages for which the average score for that age
> in the city is greater than the average score for that age across the
> nation", I can do that as follows:
> ij> select age from city_scores t1 GROUP BY age HAVING avg(t1.score) >
> (select score from natl_avg where age = t1.age);
> AGE
> -----------
> 14
> 15
> Derby allows the above query, which is good. But now assume I want to
> retrieve the same data except that I _only_ want those ages in which more
> than one person in the city took the test. I can do this by adding an
> aggregate to the query, as follows:
> select age from city_scores t1 GROUP BY age HAVING avg(t1.score) > (select
> score from natl_avg where age = t1.age AND (count(t1.age) > 1));
> This is a situation where it's reasonable to allow the "count" aggregate, but
> Derby rejects it:
> ERROR 42903: Invalid use of an aggregate function.
> The SQL standard allows this kind of usage, and other databases out there
> allow it, too. For example, if I run the above query against DB2, I will get
> a single row with value "15", as expected.
> It would be nice if Derby allowed aggregates in this kind of situation, as
> well.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
