i have three tables
customer
id | name 1 | johnorders
id | customer_id | date1 | 1 | 2013-01-012 | 1 | 2013-02-013 | 2 | 2013-03-01order_details
id | order_id | qty | cost1 | 1 | 2 | 102 | 1 | 5 | 103 | 2 | 2 | 104 | 2 | 2 | 155 | 3 | 3 | 156 | 3 | 3 | 15i 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 | 2this 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.datebut this returns the same rows for all customers, only that the qty and cost dont hav values
ليست هناك تعليقات:
إرسال تعليق