CODEWITHSUNDEEP
sqloracle-databaseaveragesqlplus
Moraqib
Moraqib

Reputation: 19

average query ORA-00936 error

SQL> SELECT consignmentNo, VoyageNo, Weight
  2  (SELECT (AVG(WEIGHT) FROM consignment), AS AVERAGE,
  3  WHERE Weight = 650,
  4  FROM consignment;
(SELECT (AVG(WEIGHT) FROM consignment), AS AVERAGE,
 *
ERROR at line 2:
ORA-00936: missing expression

average weight for a particular ship, listing consignments for the particular ship also, unable to identify the error

Upvotes: 0

Views: 91

Answers (3)

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181290

You have an extra , in your query (before AS AVERAGE) and you are missing a , after Weight. Also from and where is not in the right order. Try this:

SELECT consignmentNo, VoyageNo, Weight,
(SELECT (AVG(WEIGHT) FROM consignment) AS AVERAGE,
FROM consignment
WHERE Weight = 650;

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You seems want :

SELECT consignmentNo, VoyageNo, Weight, avg.AVERAGE 
FROM consignment CROSS JOIN 
     (SELECT AVG(WEIGHT) AS AVERAGE FROM consignment) avg
WHERE Weight = 650;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269933

Are you simply looking for group by?

SELECT VoyageNo, AVG(Weight)
FROM consignment
GROUP BY VoyageNo;

If you want the average along with the detailed information, you want a window function:

SELECT c.*, AVG(Weight) OVER (PARTITION BY VoyageNo)
FROM consignment c;

This assumes that VoyageNo is what you mean by ship.

Upvotes: 1

Related Questions