10 Replies Latest reply: Oct 19, 2013 1:54 AM by Ramin Hashimzadeh RSS

    Grouping and subgrouping

    user9019392

      Hi Experts,

       

      I am looking for select which can give me results as below. I am fairly new and tried

       

      Data

      =====

      select 'g1' group_key,'p1' prod,'pd1' prod_dsc,'m1' month ,1 sales from dual

      union all

      select 'g1','p2','pd2','m1',2 from dual

      union all

      select 'g1','p3','pd3','m2',3 from dual

      union all

      select 'g2','p4','pd4','m1',4 from dual

      union all

      select 'g2','p5','pd5','m2',5 from dual

      union all

      select 'g3','p6','pd6','m2',6 from dual

       

      ouput

      =====

      g1p3pd3m26
      p3pd3m23
      p2pd2m12
      p1pd1m11
      g2p5pd5m29
      g2p5pd5m25
      p4pd4m14
      g3p6pd6m26
      g3p6pd6m26

       

      So basically prod,prod dsc and month is not known for the group but it will be taken from the highest sales within the group. Any help is highly appreciated.

       

      Thanks,

        • 1. Re: Grouping and subgrouping
          Ramin Hashimzadeh

          hi, please try this :

          WITH T AS(

          select 'g1' group_key,'p1' prod,'pd1' prod_dsc,'m1' month ,1 sales from dual

          union all

          select 'g1','p2','pd2','m1',2 from dual

          union all

          select 'g1','p3','pd3','m2',3 from dual

          union all

          select 'g2','p4','pd4','m1',4 from dual

          union all

          select 'g2','p5','pd5','m2',5 from dual

          union all

          select 'g3','p6','pd6','m2',6 from dual

          )

          select decode(grp_id,0,null,group_key) group_key,

                 prod,

                 prod_dsc,

                 mnth,

                 sm_sales

          from(

          SELECT t.group_key,

                 max(t.prod) keep (dense_rank first order by t.sales desc) prod,

                 max(t.prod_dsc) keep (dense_rank first order by t.sales desc) prod_dsc,

                 max(t.month) keep (dense_rank first order by t.sales desc) mnth,

                 sum(t.sales) sm_sales,

                 GROUPING(t.group_key) grp,

                 GROUPING_ID (t.group_key,t.prod) grp_id

                

          FROM T

          group by rollup(t.group_key,t.prod)

          order by 1, sm_sales desc

          )

          where grp = 0

           

           

          GROUP_KEYPRODPROD_DSCMNTHSM_SALES
          g1p3pd3m26
          p3pd3m23
          p2pd2m12
          p1pd1m11
          g2p5pd5m29
          p5pd5m25
          p4pd4m14
          p6pd6m26
          g3p6pd6m26

           

           

          ----

          Ramin Hashimzade

          • 2. Re: Grouping and subgrouping
            Ramin Hashimzadeh

            with order by :

             

            WITH T AS(

            select 'g1' group_key,'p1' prod,'pd1' prod_dsc,'m1' month ,1 sales from dual

            union all

            select 'g1','p2','pd2','m1',2 from dual

            union all

            select 'g1','p3','pd3','m2',3 from dual

            union all

            select 'g3','p6','pd6','m2',6 from dual

            union all

            select 'g2','p4','pd4','m1',4 from dual

            union all

            select 'g2','p5','pd5','m2',5 from dual

            )

            select decode(grp_id,0,null,group_key) group_key,

                   prod,

                   prod_dsc,

                   mnth,

                   sm_sales,

                   row_number() over (partition by group_key order by decode(grp_id,0,null,group_key)) o

            from(

            SELECT t.group_key,

                   max(t.prod) keep (dense_rank first order by t.sales desc) prod,

                   max(t.prod_dsc) keep (dense_rank first order by t.sales desc) prod_dsc,

                   max(t.month) keep (dense_rank first order by t.sales desc) mnth,

                   sum(t.sales) sm_sales,

                   GROUPING(t.group_key) grp,

                   GROUPING_ID (t.group_key,t.prod) grp_id

            FROM T

            group by rollup(t.group_key,t.prod)

            )

            where grp = 0

             

            GROUP_KEYPRODPROD_DSCMNTHSM_SALESO
            g1p3pd3m261
            p1pd1m112
            p2pd2m123
            p3pd3m234
            g2p5pd5m291
            p5pd5m252
            p4pd4m143
            g3p6pd6m261
            p6pd6m262

             

             

            ----

            Ramin Hashimzade

            • 3. Re: Grouping and subgrouping
              Frank Kulash

              Hi,

               

              What if there happens to be a tie for the highest sales in a group?  That is, what if the exact same highest sales figure was reached by 2 or more rows in with the same group_key?

              The solutions above may produce totals rows where prod, prod_dsc and month are taken from 2 or 3 different rows of the table.  The solution below guarantees that the prod, prod_dsc and month on the totals row are all taken from the same table row, namely the row shown right after the totals row::

               

              WITH got_r_num AS

              (

                  SELECT  group_key, prod, prod_dsc, month, sales

                  ,       SUM (sales)   OVER ( PARTITION BY  group_key ) AS total_sales

                  ,       ROW_NUMBER () OVER ( PARTITION BY  group_key

                                               ORDER BY      sales DESC

                                             )                                AS r_num

                  FROM    table_x

              )

              ,       union_data AS

              (

                  SELECT    group_key, prod, prod_dsc, month

                  ,         total_sales    AS sales

                  ,         0       AS r_num

                  FROM      got_r_num

                  WHERE     r_num  = 1

                UNION ALL

                  SELECT    group_key, prod, prod_dsc, month

                  ,         sales

                  ,         r_num

                  FROM      got_r_num

              )

              SELECT    CASE

                            WHEN  r_num   = 0

                            THEN  group_key

                        END     AS group_key

              ,         prod, prod_dsc, month, sales

              FROM      union_data

              ORDER BY  union_data.group_key

              ,         r_num

              ;

              Output:

               

              GR PR PRO MO      SALES

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

              g1 p3 pd3 m2          6

                 p3 pd3 m2          3

                 p2 pd2 m1          2

                 p1 pd1 m1          1

              g2 p5 pd5 m2          9

                 p5 pd5 m2          5

                 p4 pd4 m1          4

              g3 p6 pd6 m2          6

                 p6 pd6 m2          6

              As you can see, this only displays the group_key on the totals rows. If you really want the group_key displayed on some of the other rows, explain how you decide which ones.

              • 4. Re: Grouping and subgrouping
                user9019392

                Thanks Guys highly appreciate your quick reply. I couldnt mark both as correct answers

                • 5. Re: Grouping and subgrouping
                  user9019392

                  Hi  Experts,

                   

                  Forgot to mention , how to sort by the parent sum of sales(marked red in my first post). I still need to understand the query and the functions used above.

                   

                  Thanks,

                  • 6. Re: Grouping and subgrouping
                    Ramin Hashimzadeh

                    Show what is problem? I think my query should work.

                     

                    ----

                    Ramin Hashimzade

                    • 7. Re: Grouping and subgrouping
                      Frank Kulash

                      Hi,

                       

                      As Ramin said, what's the problem?

                      Point out where the query I posted is not producing the results you want, and explain how you get those results from the given data.  If you need to post additional sample data to show the problem, then, when you post the new data, also post the exact results you want from that data.

                      • 8. Re: Grouping and subgrouping
                        user9019392

                        Hi,

                         

                        Data

                        =====

                        select 'g1' group_key,'p1' prod,'pd1' prod_dsc,'m1' month ,1 sales from dual

                        union all

                        select 'g1','p2','pd2','m1',2 from dual

                        union all

                        select 'g1','p3','pd3','m2',3 from dual

                        union all

                        select 'g2','p4','pd4','m1',4 from dual

                        union all

                        select 'g2','p5','pd5','m2',5 from dual

                        union all

                        select 'g3','p6','pd6','m2',7 from dual

                         

                        Below is the expected output :- Two changes as opose to my earlier requirement.

                         

                        1. null product at group prod column(underline)

                        2. sort is done by group total i.e. 9, 7 and 6.(highlighed bold)

                         

                        output

                        ---------

                        g2nullpd5m29
                        p5pd5m25
                        p4pd4m14
                        g3nullpd6m27
                        p6pd6m27
                        g1nullpd3m26
                        p3pd3m23
                        p2pd2m12
                        p1pd1m11

                         

                        Thanks in advance for your help,

                        • 9. Re: Grouping and subgrouping
                          Frank Kulash

                          Hi,

                           

                          Here's one way to do that:

                           

                          WITH got_r_num AS

                          (

                              SELECT  group_key, prod, prod_dsc, month, sales

                              ,       SUM (sales)   OVER ( PARTITION BY  group_key ) AS total_sales

                              ,       ROW_NUMBER () OVER ( PARTITION BY  group_key

                                                           ORDER BY      sales DESC

                                                         )                                AS r_num

                              FROM    table_x

                          )

                          ,       got_g_num       AS

                          (

                              SELECT  r.*

                              ,       DENSE_RANK () OVER (ORDER BY  total_sales DESC) AS g_num

                              FROM    got_r_num  r

                          )

                          ,       union_data AS

                          (

                              SELECT    group_key, NULL AS prod, prod_dsc, month, g_num

                              ,         total_sales    AS sales

                              ,         0       AS r_num

                              FROM      got_g_num

                              WHERE     r_num  = 1

                            UNION ALL

                              SELECT    group_key, prod, prod_dsc, month, g_num

                              ,         sales

                              ,         r_num

                              FROM      got_g_num

                          )

                          SELECT    CASE

                                        WHEN  r_num   = 0

                                        THEN  group_key

                                    END     AS group_key

                          ,         prod, prod_dsc, month, sales

                          FROM      union_data

                          ORDER BY  g_num

                          ,         r_num

                          ;

                          • 10. Re: Grouping and subgrouping
                            Ramin Hashimzadeh

                            here is my way with your order by and null :

                             

                            WITH T AS(

                            select 'g1' group_key,'p1' prod,'pd1' prod_dsc,'m1' month ,1 sales from dual

                            union all

                            select 'g1','p2','pd2','m1',2 from dual

                            union all

                            select 'g1','p3','pd3','m2',3 from dual

                            union all

                            select 'g2','p4','pd4','m1',4 from dual

                            union all

                            select 'g2','p5','pd5','m2',5 from dual

                            union all

                            select 'g3','p6','pd6','m2',7 from dual

                            )

                            -----MAIN QUERY----------------

                            SELECT group_key,

                                   prod,

                                   prod_dsc,

                                   mnth,

                                   sm_sales

                              FROM (select decode(grp_id, 0, null, group_key) group_key,

                                           prod,

                                           prod_dsc,

                                           mnth,

                                           sm_sales,

                                           row_number() over(partition by group_key order by decode(grp_id, 0, null, group_key), sm_sales DESC) o,

                                           MAX(sm_sales) OVER(PARTITION BY group_key) M_GR

                                      from (SELECT t.group_key,

                                                   t.prod,

                                                   max(t.prod_dsc) keep(dense_rank first order by t.sales desc) prod_dsc,

                                                   max(t.month) keep(dense_rank first order by t.sales desc) mnth,

                                                   sum(t.sales) sm_sales,

                                                   GROUPING(t.group_key) grp,

                                                   GROUPING_ID(t.group_key, t.prod) grp_id

                                              FROM T

                                             group by rollup(t.group_key, t.prod))

                                     where grp = 0)

                            ORDER BY M_GR DESC, O

                             

                            GROUP_KEYPRODPROD_DSCMNTHSM_SALES
                            g2pd5m29
                            p5pd5m25
                            p4pd4m14
                            g3pd6m27
                            p6pd6m27
                            g1pd3m26
                            p3pd3m23
                            p2pd2m12
                            p1pd1m11

                             

                             

                            ----

                            Ramin Hashimzade