CODEWITHSUNDEEP
sqloracle-database
DrewPratt
DrewPratt

Reputation: 61

SQL ORACLE ORA-00923: FROM keyword not found where expected

I know what this error means but I do not understand why I am getting it. I think I did the question right. Please help. Question, ERD and code below. I am using Oracle SQL Developer. Thanks in advance!

For each stock exchange, display the symbol of the stock with the highest total trade volume. Show the stock exchange name, stock symbol and total trade volume. Sort the output by the name of the stock exchange and the stock symbol.

SELECT 
  se.name,
  se.stock_ex_id,
  sl.stock_symbol,
  SUM(t.shares) AS total_trade_volume
FROM stock_exchange se
  JOIN trade t
    ON se.stock_ex_id = t.stock_ex_id
  JOIN stock_listing sl
    ON sl.stock_id = t.stock_id
   AND sl.stock_ex_id = t.stock_ex_id
HAVING SUM(t.shares) IN (SELECT MAX(total_shares)
                         FROM (SELECT stock_ex_id,
                                      stock_id,
                                      SUM(shares)
                               FROM trade
                         WHERE stock_ex_id IS NOT NULL
                         GROUP BY stock_ex_id, stock_id)
                         GROUP BY se.stock_ex_id, se.name, sl.stock_symbol
                         ORDER BY se.name, sl.stock_symbol

ERD:

enter image description here

Upvotes: 0

Views: 3138

Answers (1)

forpas
forpas

Reputation: 164194

Several syntax errors, including:
HAVING clause before GROUP BY
missing alias of a column
I tried correcting these and others:

SELECT 
  se.name,
  se.stock_ex_id,
  sl.stock_symbol,
  SUM(t.shares) AS total_trade_volume
FROM stock_exchange se 
JOIN trade t ON se.stock_ex_id = t.stock_ex_id
JOIN stock_listing sl ON sl.stock_id = t.stock_id AND sl.stock_ex_id = t.stock_ex_id
GROUP BY se.stock_ex_id, se.name, sl.stock_symbol
HAVING SUM(t.shares) IN (
  SELECT MAX(total_shares)
  FROM (
    SELECT stock_ex_id, stock_id, SUM(shares) AS total_shares
    FROM trade
    WHERE stock_ex_id IS NOT NULL
    GROUP BY stock_ex_id, stock_id
  )
)
ORDER BY se.name, sl.stock_symbol

Also, although not syntactically wrong, the IN clause is useless and instead you can use =.

Upvotes: 2

Related Questions