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

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.

Answer

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.