Calculate the sum of fields in another table using MySQL SQL query

Question

I have a schema like this: user table with attributes "user_id" and "username" and an orders table with attributes "customer_id" (FK of user_id) and "finalPrice" database schema I want to get the user with the highest price among all order combinations (basically the sum of all FinalPrice values ​​for the order, where customer_id = user_id) Can't quite figure out a solution so any support will be appreciated

select sum( (select o.final_price from `order` o where u.user_id=o.customer_id)) 
from user u group by u.user_id

is what I tried, but I keep getting the "Subquery returned more than 1 row" error message. Tried reading the documentation, but I'm still very inexperienced with SQL.

Answer 1

Based on your query You can't use SUM function like this but use internal SUM that's why it throws error like Subquery returns more than 1 row

SELECT  
u.user_id,
(SELECT SUM(o.final_price) FROM `order` o WHERE u.user_id=o.customer_id)
FROM 
user u 
GROUP BY u.user_id;