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?
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; WHERE clauses filter the individual 'input' records prior to aggregation.
HAVING clauses filter the resulting 'output' records after the aggregation.
Giving the answer posted by Martin.
HAVINGclause or not?