الثلاثاء، 24 ديسمبر 2013

Select and summarize data from three tables

i have three tables

customer

id | name 1 | john

orders

id | customer_id | date1 | 1 | 2013-01-012 | 1 | 2013-02-013 | 2 | 2013-03-01

order_details

id | order_id | qty | cost1 | 1 | 2 | 102 | 1 | 5 | 103 | 2 | 2 | 104 | 2 | 2 | 155 | 3 | 3 | 156 | 3 | 3 | 15

i need to select data so i can get the output for each order_id the summary of the order sample output. I will query the database with a specific customer id

output

date | amount | qty | order_id2013-01-01 | 70 | 7 | 12013-02-01 | 50 | 4 | 2

this is what i tried

SELECT orders.id, orders.date, SUM(order_details.qty * order_details.cost) AS amount, SUM(order_details.qty) AS qtyFROM orders LEFT OUTER JOIN order_details ON order_details.order_id=orders.id AND orders.customer_id = 1 GROUP BY orders.date

but this returns the same rows for all customers, only that the qty and cost dont hav values


View the original article here

ليست هناك تعليقات:

إرسال تعليق