This discussion is archived
7 Replies Latest reply: Aug 23, 2013 10:05 PM by ActiveSomeTimes RSS

Query help

user527060 Newbie
Currently Being Moderated

I am using oracle 11g

 

With the following data set. I want to group by on A_key and b_key, skey and get the sum of prices for 12, 24,36

 

 

with data_set as (

select 'AAA' a_key, 'BBB' b_key, 123 skey, 2 price, 12 term from dual union all

select 'AAA' a_key, 'BBB' b_key, 123 skey, 4 price, 24 term from dual union all

select 'AAA' a_key, 'BBB' b_key, 123 skey, 6 price, 36 term from dual union all

select 'AAA' a_key, 'BBB' b_key, 345 skey, 1 price, 12 term from dual union all

select 'AAA' a_key, 'BBB' b_key, 345 skey, 2 price, 24 term from dual union all

select 'AAA' a_key, 'BBB' b_key, 345 skey, 3 price, 36 term from dual union all

select 'AAA' a_key, 'BBB' b_key, 678 skey, 3 price, 12 term from dual union all

select 'AAA' a_key, 'BBB' b_key, 678 skey, 3 price, 24 term from dual union all

select 'AAA' a_key, 'BBB' b_key, 678 skey, 3 price, 36 term from dual union all

select 'XXX' a_key, 'CCC' b_key, 321 skey, 2 price, 12 term from dual union all

select 'XXX' a_key, 'CCC' b_key, 321 skey, 4 price, 24 term from dual union all

select 'XXX' a_key, 'CCC' b_key, 321 skey, 6 price, 36 term from dual union

)



with result_Set as (

select 'AAA' a_key,'BBB' b_key, 123 s1key, 345 s2key, 678 s3_key, 6 c1price, 9 c2price,12 c3price from dual union all

select 'XXX','CCC', 321 s1key, null s2key, null s3_key, 2 c1price, 4 c2price,6 c3price from dual
) select * from result_set

 

 

Thanks

  • 1. Re: Query help
    GPU Explorer
    Currently Being Moderated

    Hi,

     

    You can use rollup function to aggregate price

     

    SQL> select * from v$version;

    BANNER                                                                         
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production   
    PL/SQL Release 11.2.0.2.0 - Production                                         
    CORE 11.2.0.2.0 Production                                                     
    TNS for Linux: Version 11.2.0.2.0 - Production                                 
    NLSRTL Version 11.2.0.2.0 - Production                                         

    SQL>
    SQL> /* Formatted on 8/23/2013 2:35:18 PM (QP5 v5.163.1008.3004) */
    SQL> WITH data_set
      2       AS (SELECT 'AAA' a_key,
      3                  'BBB' b_key,
      4                  123 skey,
      5                  2 price,
      6                  12 term
      7             FROM DUAL
      8           UNION ALL
      9           SELECT 'AAA' a_key,
    10                  'BBB' b_key,
    11                  123 skey,
    12                  4 price,
    13                  24 term
    14             FROM DUAL
    15           UNION ALL
    16           SELECT 'AAA' a_key,
    17                  'BBB' b_key,
    18                  123 skey,
    19                  6 price,
    20                  36 term
    21             FROM DUAL
    22           UNION ALL
    23           SELECT 'AAA' a_key,
    24                  'BBB' b_key,
    25                  345 skey,
    26                  1 price,
    27                  12 term
    28             FROM DUAL
    29           UNION ALL
    30           SELECT 'AAA' a_key,
    31                  'BBB' b_key,
    32                  345 skey,
    33                  2 price,
    34                  24 term
    35             FROM DUAL
    36           UNION ALL
    37           SELECT 'AAA' a_key,
    38                  'BBB' b_key,
    39                  345 skey,
    40                  3 price,
    41                  36 term
    42             FROM DUAL
    43           UNION ALL
    44           SELECT 'AAA' a_key,
    45                  'BBB' b_key,
    46                  678 skey,
    47                  3 price,
    48                  12 term
    49             FROM DUAL
    50           UNION ALL
    51           SELECT 'AAA' a_key,
    52                  'BBB' b_key,
    53                  678 skey,
    54                  3 price,
    55                  24 term
    56             FROM DUAL
    57           UNION ALL
    58           SELECT 'AAA' a_key,
    59                  'BBB' b_key,
    60                  678 skey,
    61                  3 price,
    62                  36 term
    63             FROM DUAL
    64           UNION ALL
    65           SELECT 'XXX' a_key,
    66                  'CCC' b_key,
    67                  321 skey,
    68                  2 price,
    69                  12 term
    70             FROM DUAL
    71           UNION ALL
    72           SELECT 'XXX' a_key,
    73                  'CCC' b_key,
    74                  321 skey,
    75                  4 price,
    76                  24 term
    77             FROM DUAL
    78           UNION ALL
    79           SELECT 'XXX' a_key,
    80                  'CCC' b_key,
    81                  321 skey,
    82                  6 price,
    83                  36 term
    84             FROM DUAL)
    85    SELECT a_key,
    86           b_key,
    87           sKey,
    88           SUM (price),
    89           term
    90      FROM data_set where term IN (12,24,36)
    91  GROUP BY ROLLUP (a_key, b_key, sKey, term);

    A_K B_K       SKEY SUM(PRICE)       TERM                                       
    --- --- ---------- ---------- ----------                                       
    AAA BBB        123          2         12                                       
    AAA BBB        123          4         24                                       
    AAA BBB        123          6         36                                       
    AAA BBB        123         12                                                  
    AAA BBB        345          1         12                                       
    AAA BBB        345          2         24                                       
    AAA BBB        345          3         36                                       
    AAA BBB        345          6                                                  
    AAA BBB        678          3         12                                       
    AAA BBB        678          3         24                                       
    AAA BBB        678          3         36                                       

    A_K B_K       SKEY SUM(PRICE)       TERM                                       
    --- --- ---------- ---------- ----------                                       
    AAA BBB        678          9                                                  
    AAA BBB                    27                                                  
    AAA                        27                                                  
    XXX CCC        321          2         12                                       
    XXX CCC        321          4         24                                       
    XXX CCC        321          6         36                                       
    XXX CCC        321         12                                                  
    XXX CCC                    12                                                  
    XXX                        12                                                  
                               39                                                  

    21 rows selected.

     

    Thanks,

    GPU

  • 2. Re: Query help
    ActiveSomeTimes Explorer
    Currently Being Moderated

    Can you explain the logic for your second row in result set ?

     

    with data_set as (

    select 'AAA' a_key, 'BBB' b_key, 123 skey, 2 price, 12 term from dual union all

    select 'AAA' a_key, 'BBB' b_key, 123 skey, 4 price, 24 term from dual union all

    select 'AAA' a_key, 'BBB' b_key, 123 skey, 6 price, 36 term from dual union all

    select 'AAA' a_key, 'BBB' b_key, 345 skey, 1 price, 12 term from dual union all

    select 'AAA' a_key, 'BBB' b_key, 345 skey, 2 price, 24 term from dual union all

    select 'AAA' a_key, 'BBB' b_key, 345 skey, 3 price, 36 term from dual union all

    select 'AAA' a_key, 'BBB' b_key, 678 skey, 3 price, 12 term from dual union all

    select 'AAA' a_key, 'BBB' b_key, 678 skey, 3 price, 24 term from dual union all

    select 'AAA' a_key, 'BBB' b_key, 678 skey, 3 price, 36 term from dual union all

    select 'XXX' a_key, 'CCC' b_key, 321 skey, 2 price, 12 term from dual union all

    select 'XXX' a_key, 'CCC' b_key, 321 skey, 4 price, 24 term from dual union all

    select 'XXX' a_key, 'CCC' b_key, 321 skey, 6 price, 36 term from dual

    )

    , sorted as(

    select a_key,b_key,skey ,row_number() over(partition by a_key,b_key ,skey order by skey) rno ,max(price) over(partition by a_key,b_key ,skey order by skey) max_price from data_set)

    , data as (

    select row_number() over(partition by a_key,b_key order by null) rno2 ,a_key,b_key,skey,sum(max_price) over(partition by a_key,b_key order by skey) price from sorted where rno = 1 )

    select a_key,b_key,max(decode(rno2,1,skey)) s1key ,max(decode(rno2,2,skey))s2key , max(decode(rno2,3,skey)) s3key, max(decode(rno2,1,price)) c1price, max(decode(rno2,2,price)) c2price  , max(decode(rno2,3,price)) c3price  from data

    group by a_key,b_key

     

     

    A_KEYB_KEYS1KEYS2KEYS3KEYC1PRICEC2PRICEC3PRICE
    XXXCCC321--6--
    AAABBB1233456786912
  • 3. Re: Query help
    user527060 Newbie
    Currently Being Moderated

    Rollup is not going to work as I want sum of 12 months prices, 24 months prices and 36 months prices.

  • 4. Re: Query help
    user527060 Newbie
    Currently Being Moderated

    There are only single records for term12 , 24 and 36 and the prices are 2,4 and 6 respectivesly ( a_key = 'XXX' and b_key = 'BBB')

  • 5. Re: Query help
    ora1001 Explorer
    Currently Being Moderated

    With the following data set. I want to group by on A_key and b_key, skey and get the sum of prices for 12, 24,36

    just wondering where this s1key s2key came from? i thought you wanted to group by above columns which will give the below result'

     

                              12Sum   24Sum   36Sum

    AAA BBB 123    xx

    AAA BBB 345

    AAA BBB 678

    XXX CCC 321

  • 6. Re: Query help
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    Here's one way:

     

    WITH got_s_num    AS

    (

        SELECT  a_key, b_key, skey, price, term

        ,       DENSE_RANK () OVER ( PARTITION BY  a_key

                                      ,            b_key

                                       ORDER BY    skey

                                   )  AS s_num

        FROM    data_set

       WHERE term IN (12, 24, 36)

    )

    SELECT    a_key, b_key

    ,         MAX (CASE WHEN s_num =  1 THEN skey  END)   AS s1key

    ,         MAX (CASE WHEN s_num =  2 THEN skey  END)   AS s2key

    ,         MAX (CASE WHEN s_num =  3 THEN skey  END)   AS s3key

    ,         SUM (CASE WHEN term  = 12 THEN price END)   AS c1price

    ,         SUM (CASE WHEN term  = 24 THEN price END)   AS c2price

    ,         SUM (CASE WHEN term  = 36 THEN price END)   AS c3price

    FROM      got_s_num

    GROUP BY  a_key, b_key

    ORDER BY  a_key, b_key

    ;

    Ouptut (as requested):

     

    A_K B_K      S1KEY      S2KEY      S3KEY    C1PRICE    C2PRICE    C3PRICE

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

    AAA BBB        123        345        678          6          9         12

    XXX CCC        321                                2          4          6

  • 7. Re: Query help
    ActiveSomeTimes Explorer
    Currently Being Moderated

    with data_set as (

    select 'AAA' a_key, 'BBB' b_key, 123 skey, 2 price, 12 term from dual union all

    select 'AAA' a_key, 'BBB' b_key, 123 skey, 4 price, 24 term from dual union all

    select 'AAA' a_key, 'BBB' b_key, 123 skey, 6 price, 36 term from dual union all

    select 'AAA' a_key, 'BBB' b_key, 345 skey, 1 price, 12 term from dual union all

    select 'AAA' a_key, 'BBB' b_key, 345 skey, 2 price, 24 term from dual union all

    select 'AAA' a_key, 'BBB' b_key, 345 skey, 3 price, 36 term from dual union all

    select 'AAA' a_key, 'BBB' b_key, 678 skey, 3 price, 12 term from dual union all

    select 'AAA' a_key, 'BBB' b_key, 678 skey, 3 price, 24 term from dual union all

    select 'AAA' a_key, 'BBB' b_key, 678 skey, 3 price, 36 term from dual union all

    select 'XXX' a_key, 'CCC' b_key, 321 skey, 2 price, 12 term from dual union all

    select 'XXX' a_key, 'CCC' b_key, 321 skey, 4 price, 24 term from dual union all

    select 'XXX' a_key, 'CCC' b_key, 321 skey, 6 price, 36 term from dual

    )

    , data as (

    select a_key,b_key,skey,term,sum(price) over(partition by a_key,b_key,term order by term) price , row_number() over(partition by a_key,b_key ,skey order by term) rno , dense_rank() over(partition by a_key,b_key order by skey) rno2 from data_set )

    select a_key,b_key,max(decode(rno2,1,skey)) s1key ,max(decode(rno2,2,skey))s2key , max(decode(rno2,3,skey)) s3key, max(decode(rno,1,price)) c1price, max(decode(rno,2,price)) c2price  , max(decode(rno,3,price)) c3price  from data

    group by a_key,b_key

     

    A_KEYB_KEYS1KEYS2KEYS3KEYC1PRICEC2PRICEC3PRICE
    XXXCCC321--246
    AAABBB1233456786912

Legend

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