SQLTeam.com | Weblogs | Forums

Finding the total from two tables


#1

create a query that will result in one row per each customer, with their name, email, and total amount of money they've spent on orders. Sort the rows according to the total money spent, from the most spent to the least spent.

Table Customers: id, name, email
Table Orders: id, customer_id, item, price

I've been trying over and over without success, any help?


#2

Post whatever you have done so far even if it does not get you all the way.


#3

SELECT customers.name, customers.email, orders.id, + orders.price as [total] FROM customers JOIN orders ON customers.id = orders.customer_id GROUP BY total;


#4

What you said you are looking for one record per customer you would need to remove orders.id and not group by total

SELECT customers.name, customers.email, SUM(orders.price) as [total] 
FROM customers 
JOIN orders ON customers.id = orders.customer_id 
GROUP BY customers.name, customers.email;

Do you see what I have done?