Error: ORA-00937, Not a single single group function.

Context: Find the number of unreserved seats on FBN001(RouteNo) on 20/06/2021(DateOfFlight).

This is the code that I have written, the logic of the code seems okay to me so I am unsure on how to proceed. From my understanding, the context only wants to find the number of unreserved seats so there should only be one attribute which is Available Seats. Thank you so much in advance to those that help me.

FYI: The data type for ReservationNo is Number(4) and the data type for Capacity is Number(3).

    SELECT (Capacity - COUNT(ReservationNo)) AS "Available Seat"
    FROM AIRPLANE A, FLIGHT F, RECORD R
    WHERE R.FlightNo = F.FlightNo
    AND F.SerialNo = A.SerialNo
    AND RouteNo = 'FBN001' 
    AND DateOfFlight = '20/06/2021';

CodePudding user response：

Use the Aggregation function on the other column Capacity as well.

For your scenario you can try like below

SELECT (MAX(Capacity) - COUNT(ReservationNo)) AS "Available Seat"
   FROM AIRPLANE A, FLIGHT F, RECORD R
   WHERE R.FlightNo = F.FlightNo
   AND F.SerialNo = A.SerialNo
   AND RouteNo = 'FBN001' 
   AND DateOfFlight = '20/06/2021';

CodePudding user response：

Assuming that ReservationNo is in the Record table then you can aggregate only that table rather than trying to aggregate the JOIN of all three tables:

SELECT A.Capacity - R.num_reservations AS "Available Seat"
FROM   FLIGHT F
       INNER JOIN AIRPLANE A
       ON (F.SerialNo = A.SerialNo)
       INNER JOIN (
         SELECT flightno,
                COUNT(ReservationNo) AS num_reservations
         FROM   RECORD
         GROUP BY flightno
       ) R
       ON (R.FlightNo = F.FlightNo)
WHERE  F.RouteNo = 'FBN001' 
AND    F.DateOfFlight = DATE '2021-06-20';

Also:

• Use the ANSI join syntax rather than 20 year-old proprietary Oracle comma-join syntax; and
• '20/06/2021' is a string literal that looks like a date but is not a DATE data type. Oracle will try to be helpful and perform an implicit string-to-date conversion using the NLS_DATE_FORMAT session parameter but this will fail if the session parameter does not match the format of your string; it is better practice to use either a date literal DATE '2021-06-02' or to use TO_DATE to perform an explicit conversion.