Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    2

    Unanswered: SQL View Problem

    I have created tables and populated them now I am trying to create a report which uses the following view…

    Create View SchedFlightSum (FlightDate, FlightNum, AircraftSerial, SeatsReserved, SeatsAvail) AS
    select FlightSchedule.Fl_Date, FlightSchedule.Fl_Num, FlightSchedule.Pool_serial, count(customerflight.cust_num),
    sum(planetype.pl_capacity - 3)
    from FlightSchedule, PlanePool, PlaneType, customerflight
    where FlightSchedule.Pool_serial = PlanePool.Pool_Serial
    AND PlanePool.Pl_Type = PlaneType.Pl_Type
    and customerflight.fl_date = flightschedule.fl_date
    and customerflight.fl_num = flightschedule.fl_num
    group by FlightSchedule.Fl_Date, FlightSchedule.Fl_Num, FlightSchedule.Pool_serial;

    ERROR:
    select FlightSchedule.Fl_Date, FlightSchedule.Fl_Num, FlightSchedule.Pool_serial, count(customerflig

    *

    ERROR at line 2:

    ORA-00937: not a single-group group function





    I know that error typically has something to do with the Group By statement (or lack thereof), but I believe mine is correct. The error is definitely related to the "sum(planetype.pl_capacity - count(customerflight.cust_num))" part of the select statement. If I replace that aggregate count(customerflight.cust_num)) with any regular number it works, but the aggregate itself doesn’t. Is it because I have two aggregates in the same select statement? Any help you could offer would be greatly appreciated.



    Thanks

  2. #2
    Join Date
    Dec 2003
    Posts
    2
    Oops...
    On my original post I left it in the way that it DOES work. That is I left a Number where the aggregate was supposed to be. This does not return the result I want thoguh. Here is the code I recieve errors on:

    Create View SchedFlightSum (FlightDate, FlightNum, AircraftSerial, SeatsReserved, SeatsAvail) AS
    select FlightSchedule.Fl_Date, FlightSchedule.Fl_Num, FlightSchedule.Pool_serial, count(customerflight.cust_num),
    sum(planetype.pl_capacity - count(customerflight.cust_num))
    from FlightSchedule, PlanePool, PlaneType, customerflight
    where FlightSchedule.Pool_serial = PlanePool.Pool_Serial
    AND PlanePool.Pl_Type = PlaneType.Pl_Type
    and customerflight.fl_date = flightschedule.fl_date
    and customerflight.fl_num = flightschedule.fl_num
    group by FlightSchedule.Fl_Date, FlightSchedule.Fl_Num, FlightSchedule.Pool_serial;

    Thanks and sorry...

  3. #3
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106
    A suggestion is to create two views, one would be the below but with just the planetyp.pl_capacity and count(customerflight.cust_num) as separate columns (aliasing the count column--example ct_cust_flight. The other view would select all the column then do SUM(planetyp.pl_capacity) - ct_cust_flight. Also depending on your database, you could have in-line queries which is a SELECT stated in the FROM clause which you can select like a table.

    Originally posted by mugsy69
    Oops...
    On my original post I left it in the way that it DOES work. That is I left a Number where the aggregate was supposed to be. This does not return the result I want thoguh. Here is the code I recieve errors on:

    Create View SchedFlightSum (FlightDate, FlightNum, AircraftSerial, SeatsReserved, SeatsAvail) AS
    select FlightSchedule.Fl_Date, FlightSchedule.Fl_Num, FlightSchedule.Pool_serial, count(customerflight.cust_num),
    sum(planetype.pl_capacity - count(customerflight.cust_num))
    from FlightSchedule, PlanePool, PlaneType, customerflight
    where FlightSchedule.Pool_serial = PlanePool.Pool_Serial
    AND PlanePool.Pl_Type = PlaneType.Pl_Type
    and customerflight.fl_date = flightschedule.fl_date
    and customerflight.fl_num = flightschedule.fl_num
    group by FlightSchedule.Fl_Date, FlightSchedule.Fl_Num, FlightSchedule.Pool_serial;

    Thanks and sorry...

Posting Permissions

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