Using Min / Max with a Conditional Statement

Question

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

My request:

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 a clause HAVINGto filter expressions containing aggregates.

If you use MySQL, you can use the column aliases in this section for other RDBMSs you cannot.

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);  

, . WHERE, .

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;  

Answer 2

WHERE "" .

HAVING "" .

, .