7 Replies Latest reply: Aug 24, 2013 12:05 AM by Activesometimes-Oracle RSS

    Query help

    user527060

      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

          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-Oracle

            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

              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

                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

                  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

                    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-Oracle

                      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