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)
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.
Answered By – forpas