How do I get rid of a column in a HAVING clause in SQL?

Question

I was under the impression that SQL went FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. Is that not correct?

I've got the query
SELECT A.cruisingrange, AVG(E.salary) AS avgsal, A.aname
FROM employees AS E, aircraft AS A, certified AS C
WHERE A.aid = C.aid AND C.eid = E.eid
GROUP BY A.aname
HAVING A.cruisingrange > 1000;

but it breaks if I take out A.cruisingrange in the SELECT statement. I do not want it to return A.cruisingrange.

Thanks!

Answer 1

Move cruisingrange from HAVING to WHERE, and take cruisingrange out of the SELECT clause.

Answer 2

HAVING clause is similar like WHERE for conditional query, the difference is HAVING is conditional for aggregation function like AVG, SUM, COUNT, etc...in your problem, you can remove A.cruisingrange and change the 'HAVING A.cruisingrange>1000' into 'HAVING avgsal > 1000'