Results 1 to 2 of 2

Thread: Subqueries Help

  1. #1
    Join Date
    Oct 2012
    Posts
    24

    Unanswered: Subqueries Help

    Hello again all,
    Having a small error with subqueries.
    http://img703.imageshack.us/img703/6460/erdversion1.jpg
    Code so far.
    Code:
    SELECT TRIM(customer.cus_fname) ||
          DECODE(customer.cus_initial,NULL, ' ',
           ' ' || customer.cus_initial || '. ')
          || TRIM(customer.cus_lname)  Customer_Name,
          COUNT(charter.char_trip) "Number of|Flights"
    FROM x.customer
    LEFT JOIN x.charter ON customer.cus_code = charter.cus_code
    GROUP BY customer.cus_fname, customer.cus_initial, customer.cus_lname;
    HAVING COUNT(charter.char_trip) >
       (SELECT AVG(COUNT(charter.char_trip))
        from x.charter
        GROUP BY charter.cus_code)
    Error I'm getting
    Code:
    Customer                                             Number of
    Name                                                   Flights
    -------------------------------------------- -----------------
    James G. Brown                                               3 
    George Williams                                              4 
    Anne G. Farriss                                              0 
    Olette K. Smith                                              1 
    Kathy W. Smith                                               2 
    Leona K. Dunne                                               4 
    Amy B. Brian                                                 0 
    Alfred A. Ramas                                              1 
    Paul F. Olowski                                              0 
    Myron Orlando                                                3 
    
     10 rows selected 
    
    
    Error starting at line 19 in command:
    HAVING COUNT(charter.char_trip) >
    Error report:
    Unknown Command
    AVG(COUNT(CHARTER.CHAR_TRIP))
    -----------------------------
    2.57142857142857142857142857142857142857
    Expected results would be listing the names and flight counts above the average counts of flights. Similar to this.
    Code:
    Customer                                             Number of
    Name                                                   Flights
    -------------------------------------------- -----------------
    James G. Brown                                               3 
    George Williams                                              4 
    Leona K. Dunne                                               4
    Myron Orlando                                                3 
    
     4 rows selected
    Thanks for any help in advance, not sure why that error is showing. I have an example that shows an identical command

  2. #2
    Join Date
    Oct 2012
    Posts
    24
    Solved.

    Thanks anyways

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •