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)

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 !

CodePudding user response：

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

CodePudding user response：

SELECT username, 
       `date`,
       MAX(CASE WHEN `date` = CURRENT_DATE THEN price END) price,
       SUM(CASE WHEN `date` < CURRENT_DATE THEN price END) solde
FROM Commandes 
WHERE `date` <= CURRENT_DATE
GROUP BY 1, 2;

The query assume that

1. (username, `date`) is defined as UNIQUE;
2. date is stored in the column which have DATE datatype.