## Issue

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)

## Solution

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

**This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0 **