Goal: Write a query which will get the first and last name from the customer table, and the total amount of all that customer’s orders.

SELECT first,last
FROM customers C

This will get all the customers.Next we need to write a query that will get the total amount of all orders for one customer.

SELECT SUM(order_total) FROM orders
WHERE cust_id =

Now we need to combine this previous query with the first one. It will be a correlated sub query.

SELECT first,last,
(SELECT SUM(order_total) FROM orders O WHERE O.cust_id = C.id) AS total_order_amt
FROM customers C


Updated on 3/26/20 : You can also just do it like this…

select c.first, c.last, sum(o.order_total) as order_total
from customers c
join orders o on c.id = o.cust_id
group by c.first, c.last


SQL sub query challenge