SQL JOIN query problem

group byjoin;

I'm doing SQL class on Khan Academy and got stuck somewhere in the JOIN query section.

Two tables:

  • customers table with id(pk),name, email
  • Orders table with id(pk),customer_id, item, price

Question: 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.

My code looks like this but it didn't lead me to the next section. It doesn't say wrong either

SELECT sum(orders.price), customers.name, customers.email, orders.price from customers
   lEFT OUTER JOIN ORDERS
   ON customers.id = orders.customer_id
GROUP BY customers.name order by sum(orders.price) DESC;

Would appreciate an explanation.

Best Answer

This probably would've been more appropriate on the general SO site, but you're probably getting hung up on a couple things -

  1. You need to include customers.email in your GROUP BY clause
  2. Remove orders.price from the SELECT, that effectively ruins the GROUP BY you're trying to do if different orders have different prices
  3. I'm not sure how picky your course is but it's possible you need to re-order the fields in the order it asks for them
  4. Not required, but if you alias SUM(orders.price) as Total you could use the aliased field name in the ORDER BY

Using 1., 2. and 3. would result in the following query:

SELECT customers.name, customers.email, SUM(orders.price) 
FROM customers
  LEFT OUTER JOIN ORDERS
    ON customers.id = orders.customer_id
GROUP BY customers.name. customers.email 
ORDER BY SUM(orders.price) DESC;