1 Reply Latest reply on Nov 22, 2019 12:21 PM by mNem

    Grouping Sets

    Veerendra Patil

      Hi all,

      I have a report based on sample query below

      SELECT country_name,

             department_name,

             region_name,

             state_province,

             SUM (salary) sal,

             COUNT (EMPLOYEE_ID) emp_count,

      max(HIRE_DATE) max_hire_date

        FROM emp_details_view

      GROUP BY GROUPING SETS (

      (country_name,

      department_name,

      region_name,

      state_province),

      (country_name)

      )

      The output is as below

      1.png

       

      How do I get the the totals break on Only SALARY column and not on emp_count and hire_date

      Like the output should be

      1.png

      The break-up should apply only on aggregate column I specify.

       

      Please suggest

       

      Thanks,

        • 1. Re: Grouping Sets
          mNem

          select

          country_name

          , department_name

          , region_name

          , state_province

          , sal

          , decode(gid, 0, emp_count) emp_count

          , decode(gid, 0, max_hire_date) max_hire_date

          from

          (

            SELECT grouping_id(country_name,department_name,region_name,state_province) gid,

                   country_name,

                   department_name,

                   region_name,

                   state_province,

                   SUM (salary) sal,

                   COUNT (EMPLOYEE_ID) emp_count,

                   max(HIRE_DATE) max_hire_date

              FROM emp_details_view

            GROUP BY GROUPING SETS (

            (country_name,

            department_name,

            region_name,

            state_province),

            (country_name)

            )

          )

          ;

          1 person found this helpful