CODEWITHSUNDEEP
sql-server
tylkonachwile
tylkonachwile

Reputation: 2267

Cumulative value per record in particular year and month

I have got a problem with query. I have got a table. Values in the table are different, I'm presenting the same value (10) in each row to simplify and better understand the problem

| ID | UniqueProcutId | Year | Month | Value |
-----|----------------|------|-------|-------|
| 1  | wwee124        | 2015 | 1     | 10    |
| 2  | wwee124        | 2015 | 2     | 10    |
| 3  | wwee124        | 2015 | 3     | 10    |
| 4  | wwee124        | 2015 | 4     | 10    |
| 5  | wwee124        | 2015 | 5     | 10    |
| 6  | wwee124        | 2015 | 6     | 10    |
| 7  | wwee124        | 2015 | 7     | 10    |
| 8  | wwee124        | 2015 | 8     | 10    |
| 9  | wwee124        | 2015 | 9     | 10    |
| 10 | wwee124        | 2015 | 10    | 10    |
| 11 | wwee124        | 2015 | 11    | 10    |
| 12 | wwee124        | 2015 | 12    | 10    |
| 13 | wwee124        | 2016 | 1     | 10    |
| 14 | wwee124        | 2016 | 2     | 10    |
| 15 | wwee124        | 2016 | 3     | 10    |

And what I want to achive is query that will return a cumulative value for each month in year. I mean:

SELECT ID, PRODUCTID, YEAR, MONTH,
SUM(VALUE) OVER(?????)

I cant handle it :( Query should return:

| ID | UniqueProcutId | Year | Month | Value |
-----|----------------|------|-------|-------|
| 1  | wwee124        | 2015 | 1     | 10    |
| 2  | wwee124        | 2015 | 2     | 20    |
| 3  | wwee124        | 2015 | 3     | 30    |
| 4  | wwee124        | 2015 | 4     | 40    |
| 5  | wwee124        | 2015 | 5     | 50    |
| 6  | wwee124        | 2015 | 6     | 60    |
| 7  | wwee124        | 2015 | 7     | 70    |
| 8  | wwee124        | 2015 | 8     | 80    |
| 9  | wwee124        | 2015 | 9     | 90    |
| 10 | wwee124        | 2015 | 10    | 100   |
| 11 | wwee124        | 2015 | 11    | 110   |
| 12 | wwee124        | 2015 | 12    | 120   |
| 13 | wwee124        | 2016 | 1     | 10    |
| 14 | wwee124        | 2016 | 2     | 20    |
| 15 | wwee124        | 2016 | 3     | 30    |

Upvotes: 0

Views: 40

Answers (1)

PacoDePaco
PacoDePaco

Reputation: 699

You are close. Try this:

SELECT
ID,
UniqueProcutId,
[Year],
[Month],
SUM(Value) OVER(Partition BY YEAR ORDER BY [Month] ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM
YourTable

Upvotes: 1

Related Questions