I have the following code: select pil_pilotname, pil_flight_pay from pilots group by pil_pilotname, pil_flight_pay having pil_flight_pay > 2586.67 / I wanted to use avg(pil_flight_pay) instead of 2586.67. SQL, however, does not allow. I'm trying to get the flight pays that exceed the average flight pay for all pilots. What should I modify? Thanks.
Welcome to the forums. What do you mean it does not allow, any error or are you not getting expected results?
[quote user="antgaudi"] I have the following code: select pil_pilotname, pil_flight_pay from pilots group by pil_pilotname, pil_flight_pay having pil_flight_pay > 2586.67 / I wanted to use avg(pil_flight_pay) instead of 2586.67. SQL, however, does not allow. I'm trying to get the flight pays that exceed the average flight pay for all pilots. What should I modify? Thanks. [/quote] Try thisselect pil_pilotname, pil_flight_pay from pilots where pil_flight_pay > (select pil_flight_pay from pilots)
Well, a little more work is needed on that subquery, methinks. The subquery needs to return the average per pilot, so something like this: where pil_flight_pay > (select avg(x.avg_pil_flight_pay) from pilots) On a slightly unrelated note, which database platform uses "SQL Plus"?
I thought as much (about the Oracle connection). Just FYI, this forum is dedicated to Microsoft SQL Server. We're happy to help you anyway, but you may need to look elsewhere for Oracle-specific issues
I believe we should have identified on the subject line itself, as it refers SQLPlus which is an oracle product. [].
