Results 1 to 3 of 3

Thread: query help

  1. #1
    Join Date
    May 2004
    Posts
    95

    Unanswered: query help

    I've got a table with values of several years.
    If I make a select to get the result of 2 years it gives me something like this:

    ROW YEAR VALUE
    1 2005 38
    2 2006 42

    The thing is that I want to display a comparison between the 2 years, such as this:

    ROW 2005 % 2006
    1 38 10,5 42

    , the question is if there's any way to do this without making 2 union selects.

    thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Using analytical functions, query might look like this:
    Code:
    SQL> select * from yrs order by year;
    
          YEAR      VALUE
    ---------- ----------
          2003         39
          2004         35
          2005         38
          2006         42
    
    SQL> SELECT rank() over (ORDER BY YEAR) rnk,
      2         prev_year,
      3         prev_val,
      4         YEAR,
      5         value,
      6         ROUND(value/prev_val * 100, 2) - 100 ratio
      7  FROM
      8     (SELECT YEAR,
      9             value,
     10             lag(YEAR) over (ORDER BY YEAR) prev_year,
     11             lag(value) over (ORDER BY YEAR) prev_val
     12      FROM YRS
     13     );
    
           RNK  PREV_YEAR   PREV_VAL       YEAR      VALUE      RATIO
    ---------- ---------- ---------- ---------- ---------- ----------
             1                             2003         39
             2       2003         39       2004         35     -10.26
             3       2004         35       2005         38       8.57
             4       2005         38       2006         42      10.53
    
    SQL>

  3. #3
    Join Date
    May 2004
    Posts
    95
    thanks
    it is exactly what I was looking for.

Posting Permissions

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