Using Min/Max with conditional operator

Question

I am trying to run a query to find max and min values, and then use a conditional operator. However, when I try to run the following query, it gives me the error - "misuse of aggregate: min()".

My query is:

SELECT a.prim_id, min(b.new_len*36) as min_new_len, max(b.new_len*36) as max_new_len
FROM tb_first a, tb_second b
WHERE a.sec_id = b.sec_id AND min_new_len > 1900 AND max_new_len < 75000
GROUP BY a.prim_id
ORDER BY avg(b.new_len*36);

Any suggestions?

Answer 1

You need to use the HAVING clause to filter by expressions containing aggregates.

If you are using MySQL you can use the column aliases in that clause for other RDBMSs you can't.

SELECT a.prim_id,
       min(b.new_len * 36) as min_new_len,
       max(b.new_len * 36) as max_new_len
FROM   tb_first a
       JOIN tb_second b
         ON a.sec_id = b.sec_id /*<-- Use explicit JOIN syntax...*/
GROUP  BY a.prim_id
HAVING min(b.new_len * 36) > 1900
       AND max(b.new_len * 36) < 75000 
ORDER  BY avg(b.new_len * 36);  

In many RDBMSs you can also put the query into an inline view and select from that to use the column aliases instead of repeating the formulae. In that case you do use WHERE as below.

SELECT prim_id,
       min_new_len,
       max_new_len
from   (SELECT a.prim_id,
               min(b.new_len * 36) as min_new_len,
               max(b.new_len * 36) as max_new_len,
               avg(b.new_len * 36) as avg_new_len
        FROM   tb_first a
               JOIN tb_second b
                 ON a.sec_id = b.sec_id
        GROUP  BY a.prim_id) derived
WHERE  min_new_len > 1900
       AND max_new_len < 75000
ORDER  BY avg_new_len;