Mysql sum on self table select

Question

I'm looking for the right sql query to do the following operation: I need to display all record for today and for each make sum of price field for the curent user and date < today

Table Commandes: (today = 11/18/2022)

ID	username	date	price
1	user.1	11/21/2022	99.0
2	user.x	11/21/2022	99.0
3	user.1	11/18/2022	2.5
4	user.x	11/18/2022	10.0
5	user.1	11/17/2022	2.5
6	user.x	11/17/2022	20.0
7	user.1	11/16/2022	2.5
8	user.x	11/16/2022	30.0

I want:

| ID       | username | date       | price  | solde
| -------- | -------- |------------|------- |------
| 1        | user.1   | 11/18/2022 |  2.5   |  5.0
| 2        | user.x   | 11/18/2022 | 10.0   | 40.0

solde would be same as "backorder not yet payed". When Item is payed, field price is set to 0.

For now, i use this query:

SELECT * FROM Commandes WHERE (Date='11/18/2022')

And in each row I execute:

SELECT sum(price) as solde 
FROM Commandes 
WHERE (username=currentselecteduser) and (STR_TO_DATE(date, '%m/%d/%Y') < CURDATE());

That's working but really uggly !

Answer 1

Here is the solution

SELECT t1.* , 
(select sum(price) from Commandes where username = t1.username and date < CURDATE()) solde 
FROM `Commandes` as t1 
WHERE date = CURDATE()

Also would like the tell you something that, You want the total for the dates < today, so As per your logic your for user.x total will be 50 nor 40