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

["A B (JIRA)" <de...@db.apache.org>]

Allow aggregates to be used within the WHERE clause of a SELECT that is part of a HAVING clause.
------------------------------------------------------------------------------------------------

         Key: DERBY-603
         URL: http://issues.apache.org/jira/browse/DERBY-603
     Project: Derby
        Type: Improvement
  Components: SQL  
    Reporter: A B


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 contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira