I am new to SQL or MySQL so could anyone suggest me how to get the required result

My SQL query:

SELECT target - SUM(km) 
FROM table 
GROUP BY date;

I am trying to get the result.

That the given target is 5000 and i should add each day data and subtract it by 5000 and next day the remaining target will be deleted by sum.

If I enter 22-02-2022

Output: 300 (ie is sum) and 4700 (ie 5000-sum of travelled km)

And next time if I enter the next date ie 23-02-2022

Output: 800 (ie is sum) and 3900 (ie 4700-sum of travelled km)

CodePudding user response：

You can aggregate by Date to get each day's total and with SUM() window function get the running total and subtract it from the target:

SELECT Date, 
       SUM(SUM(travelled)) OVER (ORDER BY Date) total,
       5000 - SUM(SUM(travelled)) OVER (ORDER BY Date) remaining
FROM tablename
GROUP BY Date;

See the demo.