name and sum from 2 different tables-CodePudding

I am very new in mysql and i saw many answers here but i am totally confused. i need some explanation too please dont just put answer give me some explanation too because i want to learn how it works. I have 2 tables. table customer have. id , name , age table order have . id, customer_id , order_amount , order date.

I want to show all name from customer table and sum of order amount from order table according to customer.

customer_id Name    age
1           Alice   24
2           Bob     52
3           Carol   45
4           Dave    51

order_id    customer_id order_amount    order_date
1           2           50              2012-4-5
2           1           27              2012-8-1
3           2           12              2013-5-20
4           4           25              2014-1-25
5           4           30              2014-5-30
6           1           20              2014-6-22

EDIT i tried this but it gives me only bob and sum of all columns instead of separate sum of customers

SELECT customers.name, SUM(orders.order_amount) FROM `orders` INNER JOIN customers WHERE orders.customer_id = customers.customer_id;

CodePudding user response：

Joining condition must be on ON clause, not in WHERE.
You must specify for what group the sum must be calculated.

SELECT customers.name, SUM(orders.order_amount) 
FROM `orders` 
INNER JOIN customers ON orders.customer_id = customers.customer_id
GROUP BY customers.name;