CODEWITHSUNDEEP
mysql
ehsan haider
ehsan haider

Reputation: 11

name and sum from 2 different tables

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;

Upvotes: 0

Views: 49

Answers (1)

Akina
Akina

Reputation: 42632

  1. Joining condition must be on ON clause, not in WHERE.
  2. 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;

Upvotes: 0

Related Questions