CODEWITHSUNDEEP
sqloracle-database
Adam D Ruffolo
Adam D Ruffolo

Reputation: 23

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.

Upvotes: 2

Views: 118

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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.

Upvotes: 2

Related Questions