Results 1 to 4 of 4

Thread: Sql query

  1. #1
    Join Date
    Jul 2010
    Posts
    4

    Unanswered: Sql query

    Hi
    I am facing some problem while i am trying to write a query..
    I have a view the ddl is :-
    ------------------------------------------------------------------------------------------
    CREATE TABLE order_year_quater (
    quater text,
    amount bigint,
    sl_no smallint,
    years smallint
    );


    INSERT INTO order_year_quater (quater, amount, sl_no, years) VALUES ('First', 55837001, 1, 2003);
    INSERT INTO order_year_quater (quater, amount, sl_no, years) VALUES ('Second', 52264543, 2, 2003);
    INSERT INTO order_year_quater (quater, amount, sl_no, years) VALUES ('Third', 62627371, 3, 2003);
    INSERT INTO order_year_quater (quater, amount, sl_no, years) VALUES ('Fourth', 85361952, 4, 2003);
    INSERT INTO order_year_quater (quater, amount, sl_no, years) VALUES ('First', 44978084, 1, 2002);
    INSERT INTO order_year_quater (quater, amount, sl_no, years) VALUES ('Second', 61209791, 2, 2002);
    INSERT INTO order_year_quater (quater, amount, sl_no, years) VALUES ('Third', 45892600, 3, 2002);
    INSERT INTO order_year_quater (quater, amount, sl_no, years) VALUES ('Fourth', 34382588, 4, 2002);
    INSERT INTO order_year_quater (quater, amount, sl_no, years) VALUES ('First', 67815, 1, 2001);
    INSERT INTO order_year_quater (quater, amount, sl_no, years) VALUES ('Second', NULL, 2, 2001);
    INSERT INTO order_year_quater (quater, amount, sl_no, years) VALUES ('Third', 16198, 3, 2001);
    INSERT INTO order_year_quater (quater, amount, sl_no, years) VALUES ('Fourth', 17591, 4, 2001);
    INSERT INTO order_year_quater (quater, amount, sl_no, years) VALUES ('First', NULL, 1, 2000);
    INSERT INTO order_year_quater (quater, amount, sl_no, years) VALUES ('Second', 0, 2, 2000);
    INSERT INTO order_year_quater (quater, amount, sl_no, years) VALUES ('Third', 4797, 3, 2000);
    INSERT INTO order_year_quater (quater, amount, sl_no, years) VALUES ('Fourth', 3439, 4, 2000);

    --------------------------------------------------------------------------------------------



    I want to se the amount of the same quater in the previous year ex:

    year quater amount previous_year_amount
    2003 first 30000 25000


    like this
    plz help me to solve the problem
    thanks
    Bijendra

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    what sql have you written so far and what results does it give you?

  3. #3
    Join Date
    Jul 2010
    Posts
    4
    I do not understand what to write
    please give me some queries which is applicable here.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Code:
    select quater, 
           years, 
           amount,
           lag(amount) over (partition by quater order by years asc) as prev_year_amount
    from order_year_quater
    Btw: what's a "quater" (sorry if that sounds stupid, but I'm not a native speaker)

Posting Permissions

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