Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004

    Unanswered: Calculating cumulative values

    Hi all,

    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.


  2. #2
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 5
    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").

  3. #3
    Join Date
    May 2004
    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.

  4. #4
    Join Date
    May 2004
    Dominican Republic
    You need to understand what Littlefoot is saying: you need to PARTITION BY the four digits of the year, not the whole date.

  5. #5
    Join Date
    May 2004
    Just got it.....

    Many thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts