[mugsy69]

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

[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...

[dmmac]

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