CODEWITHSUNDEEP
sql
jAshton
jAshton

Reputation: 47

How to return a Cumulative Value based on Criteria

I have the following table and based on the MonthID I want to retrieve the Cumulative Value. ie: For InvoiceID 1 and MonthID 26, I want to add MonthID 24, 25 and 26 reportMonthVal.

Table

enter image description here

I would like to retrieve the following

Results

enter image description here

Any help or pointing me in the right direction would be much appreciated.

Upvotes: 0

Views: 30

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32011

use window function

select *, sum(Reportmonthval) over( partition by invoiceid order by monthid ) asYtval
from table_name

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Use the cumulative sum window function:

select t.*,
       sum(t.reportmonthval) over (partition by t.invoiceid order by t.monthid) as ytdval
from t;

Upvotes: 2

Related Questions