SQL multi table query problem using Inner Join

Question

I'm trying to solve this question using Inner Join but I keep getting errors

List the customer number, order number, order date, and order total for each order with a total that exceeds $500. Assign the column name ORDER_TOTAL to the column that displays order totals. Order the results by order number.

ORDERS table has CUSTOMER_NUM, ORDER_NUM, and ORDER_DATE

ORDER_LINE has ORDER_NUM, ITEM_NUM, NUM_ORDERED, and QOUTED_PRICE

This is my solution:

SELECT CUSTOMER_NUM, ORDERS.ORDER_NUM, ORDER_DATE, SUM(QUOTED_PRICE) AS ORDER_TOTAL     
FROM ORDERS     
INNER JOIN ORDER_LINE ON ORDERS.ORDER_NUM = ORDER_LINE.ORDER_NUM    
GROUP BY ORDER_NUM     
HAVING ORDER_TOTAL > 500;

The error I'm getting:

Error starting at line : 61 in command -    
SELECT CUSTOMER_NUM, ORDERS.ORDER_NUM, ORDER_DATE, SUM(QUOTED_PRICE) AS ORDER_TOTAL     
FROM ORDERS     
INNER JOIN ORDER_LINE ON ORDERS.ORDER_NUM = ORDER_LINE.ORDER_NUM    
GROUP BY ORDER_NUM     
HAVING ORDER_TOTAL > 500

Error at Command Line : 65 Column : 8
Error report -

SQL Error: ORA-00904: "ORDER_TOTAL": invalid identifier    
00904. 00000 -  "%s: invalid identifier"

Any suggestions?

GMB · Accepted Answer · 2020-04-26 20:39:44Z

2

Oracle does not allow reusing aliases defined in the SELECT clause in the GROUP BY clause. You need to repeat the expression. Also, all non-aggregated columns must appear in the GROUP BY clause.

So:

SELECT o.CUSTOMER_NUM, o.ORDER_NUM, o.ORDER_DATE, SUM(ol.QUOTED_PRICE) AS ORDER_TOTAL
FROM ORDERS o
INNER JOIN ORDER_LINE ol ON ol.ORDER_NUM = o.ORDER_NUM
GROUP BY o.CUSTOMER_NUM, o.ORDER_NUM, o.ORDER_DATE
HAVING SUM(ol.QUOTED_PRICE) > 500;

Note that I modified your query to use table aliases and did prefix each column with the table it belongs to; these are good practices that make the query easier to read and write, and avoid ambiguity in regard to which table each column belongs to.

answered Apr 26, 2020 at 20:39

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL multi table query problem using Inner Join

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related