var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Calculating cumulative values
I'm trying to make a SELECT that gives me a field with cumulative values over the year.
The table schema is:
date value description
200402 2 xpto
200408 1 xpto
200411 1 xpto
200501 1 xpto
200502 1 xpto
200503 3 xpto
I'm using analytical functions to get the result that I want, but the result isn't exactly how I want.
The SELECT is:
SELECT SUM(value) OVER (PARTITION BY description ORDER BY date) cum_val, value, date
FROM table inputs;
The result that I'm getting is:
cum_val value date
2 2 200402
3 1 200408
4 1 200411
5 1 200501
1 1 200502
4 3 200503
Has it shows, it cumulates over a year, and it's not what I want.
As you've put it, 'date' column is NOT A DATE. It is either a number, or a character column. If you want to get cummulative per year, you'll have to extract year from current value (first 4 "characters").
Thanks for the reply, but even if I convert the date field to a real date column (for example all as the first day of the month) the result is the same.
You need to understand what Littlefoot is saying: you need to PARTITION BY the four digits of the year, not the whole date.
Just got it.....