Mysql LEFT JOIN and SUM and show 0 instead of NULL

Question

I have first table named "orders" and 2nd table named "details"

orders :
order_id
1
2
3

details :
id | order_id | qty
1  |  1       | 2
2  |  1       | 3

How do I show as follows ?

order_id | total
1        | 5
2        | 0
3        | 0

I tried this query but didn't work :

SELECT *, SUM(qty) AS total
FROM order o
LEFT JOIN details d
  ON o.order_id = d.order_id

Answer 1

Try this:

SELECT o.order_id, IFNULL(SUM(d.qty),0) AS total
FROM orders o
    LEFT JOIN details d ON o.order_id = d.order_id
 GROUP BY order_id

Answer 2

This will do what you are asking. It will replace the null values with 0 if there is an order with no detail record.

SELECT o.order_id, 
       COALESCE(sum(d.qty), 0)
FROM orders o
LEFT JOIN details d ON o.order_id = d.order_id
GROUP BY o.order_id;

Link to SQL Fiddle Example.