1 Reply Latest reply on Jul 24, 2018 12:39 PM by Gaz in Oz

    Grand Totals and Sub Totals in SQL Developer Reports

    Shiva Ramachandran

      Hi

       

      I want to get Subtotals and Grand totals in in SQL Developer reports.

       

      Can anybody guide how to do it.

       

      Regards

       

      Shiva

        • 1. Re: Grand Totals and Sub Totals in SQL Developer Reports
          Gaz in Oz

          Perhaps sql developer report using GROUP BY GROUPING SETS for example.

          with XXNI_OM_DETAILS as (
             select 20145 account, 'Sales' account_desc, 'USA-TX' location, 98456 amount from dual union all
             select 20145 account, 'Sales' account_desc, 'USA-TX' location, 45231 amount from dual union all
             select 20145 account, 'Sales' account_desc, 'USA-CA' location, 34211 amount from dual union all
             select 20145 account, 'Sales' account_desc, 'USA-WI' location, 98456 amount from dual union all
             select 20145 account, 'Sales' account_desc, 'USA-WI' location, 98456 amount from dual
          )
          SELECT decode(grouping_id(account, account_desc, location),
                 max(grouping_id(account, account_desc, location)) over(),
                'grand total',
                 decode(grouping(account), 1, 'Subtotal()')) section
                ,ACCOUNT
                ,ACCOUNT_DESC
                ,LOCATION
                ,SUM(AMOUNT)
          FROM XXNI_OM_DETAILS
          GROUP BY GROUPING SETS ( (rownum,account, account_desc, location)
                                  , rollup(location) -- to get the Subtotals
                            );
          
          SECTION        ACCOUNT ACCOU LOCATI SUM(AMOUNT)
          ----------- ---------- ----- ------ -----------
                           20145 Sales USA-CA       34211
          Subtotal()                   USA-CA       34211
                           20145 Sales USA-TX       98456
                           20145 Sales USA-TX       45231
          Subtotal()                   USA-TX      143687
                           20145 Sales USA-WI       98456
                           20145 Sales USA-WI       98456
          Subtotal()                   USA-WI      196912
          grand total                              374810