How do I count and sum from different tables (SQL)

Question

I am trying to write an SQL statement that does this:

For each customer that has an order, I need to list the customer's number, the number of orders that customer has, the total quantity of items on those orders and the total price for those items. I then to need sort the result by the customer number.

I've got the below code. It works, but the results are incorrect. For 1 customer it says the order quantity is 2 but there is only 1 order and the price is coming out as if it's 2 orders. Another customer has 3 orders but it's showing 4. I'm assuming I'm missing a join function?

I have 3 tables, a Customer Table (Customer_Name), Orders Table (order_num, order_date, customer_num), Items Table (item_num, order_num, quantity, total_price)

SELECT   customer.customer_num AS "Customer Number",
         COUNT(DISTINCT orders.order_num) AS "Order Amount",
         SUM(items.quantity) AS "Quantity of Items",
         SUM(items.total_price) AS "Total Price w/o shipping"
FROM     items, orders, customer
WHERE    customer.customer_num = orders.customer_num
AND      orders.order_num = items.order_num
GROUP BY customer.customer_num
ORDER BY customer.customer_num ASC;

Any help would be great. Thanks.

Youre having issues with duplication due to incorrect JOIN issue. You need to join on all the appropriate columns to reduce duplication. ALSO, join duplication occurs due to many - to - one scenarios, where the duplication is expected results but does screw up thing, especially aggs. — Doug Coats
– Doug Coats, Commented Sep 6, 2020 at 19:11
Firstly you should use ANSI standard joins rather than traditional Oracle join. Secondly, you should share some sample data and expecred result for others to help you better. — Atif
– Atif, Commented Sep 6, 2020 at 19:14

Gordon Linoff · Accepted Answer · 2020-09-06 19:51:16Z

2

Logically, your query looks correct, but it is poorly written using commas in the FROM clause. In addition, you don't need to join to the customers table.

So, I would recommend:

SELECT o.customer_num AS "Customer Number",
       COUNT(DISTINCT o.order_num) AS "Order Amount",
       SUM(i.quantity) AS "Quantity of Items",
       SUM(i.total_price) AS "Total Price w/o shipping"
FROM orders o JOIN
     items i
     ON o.order_num = i.order_num
GROUP BY o.customer_num
ORDER BY o.customer_num ASC;

The COUNT(DISTINCT) should be doing what you want in terms of counting orders.

answered Sep 6, 2020 at 19:51

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

2 Comments

Adam D Ruffolo Over a year ago

Hi Gordon, thank you for taking the time, I appreciate it. I am taking an Oracle Programming class and the text book they are using is Oracle 12c by Casteel which is why my code looks the way it does (I'm on Chapters 1-5). I tried something similar and it produces the same results, so this is probably right. 1 question, in FROM you have orders o and items i and then you use o and i in the code. At that FROM area are you basically assigning i and o as the respective table names? Again, thanks for your time.

Gordon Linoff Over a year ago

@AdamDRuffolo . . . It is very sad that a class would use a textbook with such out-of-date SQL. It is not like JOIN was introduced last year. It has been part of the standard since the 1990s and supported by Oracle for a long, long time.

Collectives™ on Stack Overflow

How do I count and sum from different tables (SQL)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related