1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Rollup Data SQL query

Discussion in 'SQL PL/SQL' started by karthik.rajashekaran, May 5, 2015.

  1. karthik.rajashekaran

    karthik.rajashekaran Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hi I have query as below which gives me detailed records

    SELECT
    FEEENTRYTYPE,
    EFFECTIVE_DATE,
    ACCOUNTID,
    FUNDSECURITYID,
    FIXEDFEEAMOUNT,
    INCENTIVEFEEAMOUNT
    FROM
    TABLE A

    Attached is the result of the query which gives detailed records as in screenshot 1
    screenshot1.PNG

    I need to get output both detailed and rolled up data as example in screenshot 2 in which column FIXEDFEEAMOUNT and INCENTIVEFEEAMOUNT is SUM of values and LOSSCARRYFORWARD is set to the last value for the Month. AS HIGHLIGHTED IN ORANGE I NEED TO GET THAT TOO
    screenshot2.PNG
     
  2. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Ok -- well there are several ambiguities and problems with your post:

    First -- June has only 30 days, so there isn't going to be a rollup value for 31-JUN-14.
    Second -- your SELECT example has a column FEEENTRYTYPE which isn't in the sample results shown.
    Third -- your sample results have LOSSCARRYFORWARD and RANKING which aren't in your SELECT example.
    Fourth -- it's not clear how the RANKING, ACCOUNTID and FUNDSECURITYID fit into the rollup. Presumably they included with the EFFECTIVE_DATE as the fixed values the data is rolled up to.

    That said, to get both aggregated and non-aggregated data, you'll likely need to UNION your existing query with one rolled to the level you want (and having the same column list). I would expect the result to look something like the following (without creating and populating the table I can't be exact):

    Code (Text):
    SELECT EFFECTIVE_DATE,
           ACCOUNTID,
           FUNDSECURITYID,
           FIXEDFEEAMOUNT,
           INCENTIVEFEEAMOUNT,
           LOSSCARRYFORWARD,
           RANKING
    FROM   TABLE A
    UNION ALL
    SELECT EFFECTIVE_DATE,
           ACCOUNTID,
           FUNDSECURITYID,
           SUM(FIXEDFEEAMOUNT) AS FIXEDFEEAMOUNT,
           SUM(INCENTIVEFEEAMOUNT) AS INCENTIVEFEEAMOUNT,
           MAX(LOSSCARRYFORWARD) AS LOSSCARRYFORWARD,
           RANKING
    FROM   TABLE A
    WHERE  EFFECTIVE_DATE = LAST_DAY(EFFECTIVE_DATE)
    GROUP BY EFFECTIVE_DATE,
             ACCOUNTID,
             FUNDSECURITYID,
             RANKING;