SQL query to get the result of sum travelled on date and target 5000 (rolling subtraction should get subtracted everyday date in ordered date)

Question

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

Date	travelled
22-2-2011	100km
22-2-2011	200km
30-2-2011	200km
23-2-2011	600km
23-2-2011	200km

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)

Answer 1

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.