SQL Server Performance

Using "having" on SQL Plus

Discussion in 'Getting Started' started by antgaudi, Apr 19, 2009.

  1. antgaudi New Member

    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.
  2. satya Moderator

    Welcome to the forums.
    What do you mean it does not allow, any error or are you not getting expected results?
  3. Madhivanan Moderator

    [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)
  4. Adriaan New Member

    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"?
  5. antgaudi New Member

    I installed Oracle 10g and use SQL Plus for the queries.
    Thanks for the input, everyone!
  6. Adriaan New Member

    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
  7. satya Moderator

    I believe we should have identified on the subject line itself, as it refers SQLPlus which is an oracle product. [:)].

Share This Page