[jira] [Updated] (DERBY-603) Allow aggregates to be used within the WHERE clause of a SELECT that is part of a HAVING clause.

["Mamta A. Satoor (JIRA)" <ji...@apache.org>]

     [ 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