This discussion is archived
10 Replies Latest reply: Oct 18, 2013 11:54 PM by Ramin Hashimzadeh RSS

Grouping and subgrouping

user9019392 Newbie
Currently Being Moderated

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 Expert
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

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

  • 5. Re: Grouping and subgrouping
    user9019392 Newbie
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

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

     

    ----

    Ramin Hashimzade

  • 7. Re: Grouping and subgrouping
    Frank Kulash Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points