CODEWITHSUNDEEP
sqloracle-databasejdbc
mpang
mpang

Reputation: 311

SQL Command Not Properly Ended (Nested Aggregation with Group-by)

I keep getting this error when I tried to execute this query, although I couldn't figure out what went wrong. I'm using Oracle and JDBC.

Here's the query:

SELECT Temp.flight_number, Temp.avgprice 
FROM (SELECT P.flight_number, AVG (P.amount) AS avgprice 
        FROM purchase P 
       GROUP BY P.flight_number) AS Temp 
WHERE Temp.avgprice = (SELECT MAX (Temp.avgprice) 
                         FROM Temp)

I'm trying to get the maximum of average price of the tickets that customers have booked, group by flight_number.

Upvotes: 2

Views: 1693

Answers (2)

Teja
Teja

Reputation: 13524

SELECT Temp.flight_number, Temp.avgprice 
FROM (SELECT P.flight_number, 
             AVG (P.amount) AS avgprice 
      FROM purchase P 
      GROUP BY P.flight_number) Temp 
WHERE Temp.avgprice = (SELECT MAX (Temp1.avgprice) 
                       FROM (SELECT P.flight_number,
                                    AVG(P.amount) AS avgprice 
                             FROM purchase P 
                             GROUP BY P.flight_number) temp1
                       );

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231661

A few issues.

  1. You cannot use AS in Oracle to alias a table name
  2. You can't refer to the alias TEMP in the subquery like that.

Using analytic functions is generally going to be the most efficient approach.

SELECT flight_number,
       avgprice
  FROM (
    SELECT t.flight_number, 
           t.avgprice,
           rank() over (order by t.avgprice desc) rnk
      FROM (SELECT P.flight_number, 
                   AVG (P.amount) AS avgprice 
              FROM purchase P 
             GROUP BY P.flight_number) t
    )
 WHERE rnk = 1

You could also do something like this with subquery factoring

WITH temp AS (
  SELECT P.flight_number, 
         AVG (P.amount) AS avgprice 
   FROM purchase P 
  GROUP BY P.flight_number 
)
SELECT flight_number,
       avgprice
  FROM temp
 WHERE avgprice = (SELECT MAX(avgprice)
                     FROM temp)

Upvotes: 2

Related Questions