CODEWITHSUNDEEP
sql
Smith
Smith

Reputation: 5959

Select all orders by one customer

I have three tables, orders, orders_details and customers. I need to select orders by one customer for the orders table so I did this

orders columns:

id
customer_id
created
vat
discount
amount
paid

orders_details columns:

id
order_id
cost
qty
product

The SQL I used

SELECT 
    orders.*, 
    SUM(orders_details.qty*orders_details.cost) as amount, 
    SUM(orders_details.qty) AS qty 
FROM 
    orders, 
    orders_details, 
    customers 
WHERE 
    orders.customer_id = customers.id 
    AND orders_details.order_id = orders.id 
    AND orders.customer_id = 1

but I am getting a wrong qty of 30 instead of 20 and the amount is wrong

Upvotes: 0

Views: 1548

Answers (2)

jpw
jpw

Reputation: 44921

If you want to aggregate per order you need a GROUP BY clause. Also you should use proper JOIN syntax, and might consider using aliases to make the query more compact.

SELECT 
    o.*, 
    SUM(od.qty * od.cost) AS amount, 
    SUM(od.qty) AS qty 
FROM orders o
INNER JOIN orders_details od ON od.order_id = o.id
INNER JOIN customers c ON o.customer_id = c.id     -- not used, might be excluded
WHERE o.customer_id =1
GROUP BY o.id

Depending on what database system you are using you might need to include all columns referenced in o.* in the GROUP BY:

GROUP BY o.id, o.customer_id, o.created, o.vat, o.discount, o.amount, o.paid

Last note: as you don't seem to use any data from the customers table you probably could exclude that table altogether.

Upvotes: 5

Constantinos
Constantinos

Reputation: 1208

You're missing a GROUP BY clause which I'm guessing should be on orders.id.

Upvotes: 2

Related Questions