Forum Stats

  • 3,855,344 Users
  • 2,264,499 Discussions
  • 7,905,975 Comments

Discussions

getting total sum and pivoting scenario

elmasduro
elmasduro Member Posts: 424
edited Nov 8, 2018 9:15PM in SQL & PL/SQL

hi all,

consider the following data

WITH DATA AS (

select 'bell' company, 'out' status, 27 eweek, 27 fweek, 0 rnk, 6 cnt from dual union all

select 'bell' company, 'out' status, 28 eweek, 28 fweek, 0 rnk, 71 cnt from dual union all

select 'bell' company, 'out' status, 29 eweek, 29 fweek, 0 rnk, 67 cnt from dual union all

select 'bell' company, 'out' status, 30 eweek, 30 fweek, 0 rnk, 30 cnt from dual union all

select 'bell' company, 'out' status, 31 eweek, 31 fweek, 0 rnk, 40 cnt from dual union all

select 'bell' company, 'out' status, 32 eweek, 32 fweek, 0 rnk, 54 cnt from dual union all

select 'bell' company, 'out' status, 33 eweek, 33 fweek, 0 rnk, 63 cnt from dual union all

select 'bell' company, 'out' status, 34 eweek, 34 fweek, 0 rnk, 121 cnt from dual union all

select 'bell' company, 'out' status, 35 eweek, 35 fweek, 0 rnk, 118 cnt from dual union all

select 'bell' company, 'out' status, 36 eweek, 36 fweek, 0 rnk, 81 cnt from dual union all

select 'bell' company, 'out' status, 37 eweek, 37 fweek, 0 rnk, 124 cnt from dual union all

select 'bell' company, 'out' status, 38 eweek, 38 fweek, 0 rnk, 82 cnt from dual union all

select 'bell' company, 'out' status, 39 eweek, 39 fweek, 0 rnk, 104 cnt from dual union all

select 'bell' company, 'out' status, 40 eweek, 40 fweek, 0 rnk, 83 cnt from dual union all

select 'bell' company, 'out' status, 41 eweek, 41 fweek, 0 rnk, 16 cnt from dual union all

select 'bell' company, 'out' status, 42 eweek, 42 fweek, 0 rnk, 11 cnt from dual union all

select 'bell' company, 'out' status, 43 eweek, 43 fweek, 0 rnk, 2 cnt from dual union all

select 'bell' company, 'out' status, 27 eweek, 28 fweek, 1 rnk, 146 cnt from dual union all

select 'bell' company, 'out' status, 28 eweek, 29 fweek, 1 rnk, 256 cnt from dual union all

select 'bell' company, 'out' status, 29 eweek, 30 fweek, 1 rnk, 278 cnt from dual union all

select 'bell' company, 'out' status, 30 eweek, 31 fweek, 1 rnk, 298 cnt from dual union all

select 'bell' company, 'out' status, 31 eweek, 32 fweek, 1 rnk, 277 cnt from dual union all

select 'bell' company, 'out' status, 32 eweek, 33 fweek, 1 rnk, 345 cnt from dual union all

select 'bell' company, 'out' status, 33 eweek, 34 fweek, 1 rnk, 335 cnt from dual union all

select 'bell' company, 'out' status, 34 eweek, 35 fweek, 1 rnk, 261 cnt from dual union all

select 'bell' company, 'out' status, 35 eweek, 36 fweek, 1 rnk, 294 cnt from dual union all

select 'bell' company, 'out' status, 36 eweek, 37 fweek, 1 rnk, 270 cnt from dual union all

select 'bell' company, 'out' status, 37 eweek, 38 fweek, 1 rnk, 253 cnt from dual union all

select 'bell' company, 'out' status, 38 eweek, 39 fweek, 1 rnk, 362 cnt from dual union all

select 'bell' company, 'out' status, 39 eweek, 40 fweek, 1 rnk, 273 cnt from dual union all

select 'bell' company, 'out' status, 40 eweek, 41 fweek, 1 rnk, 145 cnt from dual union all

select 'bell' company, 'out' status, 41 eweek, 42 fweek, 1 rnk, 61 cnt from dual union all

select 'bell' company, 'out' status, 42 eweek, 43 fweek, 1 rnk, 28 cnt from dual union all

select 'bell' company, 'out' status, 27 eweek, NULL fweek, -99999 rnk, 4 cnt from dual union all

select 'bell' company, 'out' status, 28 eweek, NULL fweek, -99999 rnk, 8 cnt from dual

)

i want to pivot the data to transform columns into row. my pivot query is working ok but i want to add an additional column which is not part of the pivoting.

for example,  i want to display the following output

COMPANY STATUS RNK BILL1_VALUE   BILL2_VALUE      BILL3_VALUE      BILL4_VALUE      BILL5_VALUE      BILL6_VALUE   

bell out        0                                6               71                          67                          30                         40                        54                      

bell out        1                                                146                        256                          278                       298                        277                           

BILL7_VALUE      BILL8_VALUE          BILL9_VALUE          BILL10_VALUE      BILL11_VALUE      BILL12_VALUE    

63                               121                        118                         294                           270                          253

345                              335                      261                          81                                124                      82

BILL13_VALUE      BILL14_VALUE      BILL15_VALUE      BILL16_VALUE      BILL17_VALUE   NULL_VALUE

                    362                         273                         145                     61                               28                   4

                    104                          83                         16                          11                              2                    8

If you notice the last column(null_value), i want to include it in my query but dont know how to do it since i am doing pivoting.

if i include it as part of pivot, my output is mess up.

in addition to null_value column, i want to add the total so my final output should be as follow:

COMPANY STATUS RNK BILL1_VALUE   BILL2_VALUE      BILL3_VALUE      BILL4_VALUE      BILL5_VALUE      BILL6_VALUE    

bell out        0                                6               71                          67                          30                         40                        54                       

bell out        1                                               146                        256                        278                         298                      277                          

Total                                             6              217                        323                       308                           338                    331                   

BILL7_VALUE      BILL8_VALUE     BILL9_VALUE

63                                    121              118

345                                 335             261

408                                  456             379

BILL10_VALUE      BILL11_VALUE      BILL12_VALUE      BILL13_VALUE      BILL14_VALUE      BILL15_VALUE     

294                           270                          253                     362                         273                         145                             

81                                124                      82                        104                          83                         16                              

375                            394                        365                    466                         356                              161                          

BILL16_VALUE      BILL17_VALUE   NULL_VALUE   TOTAL

    61                               28                   4                      2260

    11                               2                    8                       2742

   72                              30                    12                    4997

the total row is a sum of all bill columns (vertically) and the total colum is the sum of all bill columns horizontally.  in addition i add null_value column which is not part of my pivot query.

my query looks like this

SELECT *

FROM (select company,status,fweek,rnk,cnt from DATA where fweek<> -99999)

PIVOT (

        SUM(CNT) VALUE

        FOR fweek in ( 27 bill1, 28 bill2, 29 bill3, 30 bill4, 31 bill5, 32 bill6, 33 bill7, 34 bill8, 35 bill9, 36 bill10, 37 bill11, 38 bill12, 39 bill13, 40 bill14, 41 bill15, 42 bill16,43 bill17)

        )

this query will pivot my data but does  not have the total row, total column and null_value since i dont know how to incorporate that in my query.  i was reading about rollup but not very familiar.

can someone help me with my query and produce the output above? i am  using oracle 11g.   thanks in advance

Tagged:
mNem

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,510 Red Diamond
    edited Nov 8, 2018 1:39AM Answer ✓

    Hi,

    elmasduro wrote:hi all,consider the following dataWITH DATA AS (select 'bell' company, 'out' status, 27 eweek, 27 fweek, 0 rnk, 6 cnt from dual union allselect 'bell' company, 'out' status, 28 eweek, 28 fweek, 0 rnk, 71 cnt from dual union allselect 'bell' company, 'out' status, 29 eweek, 29 fweek, 0 rnk, 67 cnt from dual union allselect 'bell' company, 'out' status, 30 eweek, 30 fweek, 0 rnk, 30 cnt from dual union allselect 'bell' company, 'out' status, 31 eweek, 31 fweek, 0 rnk, 40 cnt from dual union allselect 'bell' company, 'out' status, 32 eweek, 32 fweek, 0 rnk, 54 cnt from dual union allselect 'bell' company, 'out' status, 33 eweek, 33 fweek, 0 rnk, 63 cnt from dual union allselect 'bell' company, 'out' status, 34 eweek, 34 fweek, 0 rnk, 121 cnt from dual union allselect 'bell' company, 'out' status, 35 eweek, 35 fweek, 0 rnk, 118 cnt from dual union allselect 'bell' company, 'out' status, 36 eweek, 36 fweek, 0 rnk, 81 cnt from dual union allselect 'bell' company, 'out' status, 37 eweek, 37 fweek, 0 rnk, 124 cnt from dual union allselect 'bell' company, 'out' status, 38 eweek, 38 fweek, 0 rnk, 82 cnt from dual union allselect 'bell' company, 'out' status, 39 eweek, 39 fweek, 0 rnk, 104 cnt from dual union allselect 'bell' company, 'out' status, 40 eweek, 40 fweek, 0 rnk, 83 cnt from dual union allselect 'bell' company, 'out' status, 41 eweek, 41 fweek, 0 rnk, 16 cnt from dual union allselect 'bell' company, 'out' status, 42 eweek, 42 fweek, 0 rnk, 11 cnt from dual union allselect 'bell' company, 'out' status, 43 eweek, 43 fweek, 0 rnk, 2 cnt from dual union allselect 'bell' company, 'out' status, 27 eweek, 28 fweek, 1 rnk, 146 cnt from dual union allselect 'bell' company, 'out' status, 28 eweek, 29 fweek, 1 rnk, 256 cnt from dual union allselect 'bell' company, 'out' status, 29 eweek, 30 fweek, 1 rnk, 278 cnt from dual union allselect 'bell' company, 'out' status, 30 eweek, 31 fweek, 1 rnk, 298 cnt from dual union allselect 'bell' company, 'out' status, 31 eweek, 32 fweek, 1 rnk, 277 cnt from dual union allselect 'bell' company, 'out' status, 32 eweek, 33 fweek, 1 rnk, 345 cnt from dual union allselect 'bell' company, 'out' status, 33 eweek, 34 fweek, 1 rnk, 335 cnt from dual union allselect 'bell' company, 'out' status, 34 eweek, 35 fweek, 1 rnk, 261 cnt from dual union allselect 'bell' company, 'out' status, 35 eweek, 36 fweek, 1 rnk, 294 cnt from dual union allselect 'bell' company, 'out' status, 36 eweek, 37 fweek, 1 rnk, 270 cnt from dual union allselect 'bell' company, 'out' status, 37 eweek, 38 fweek, 1 rnk, 253 cnt from dual union allselect 'bell' company, 'out' status, 38 eweek, 39 fweek, 1 rnk, 362 cnt from dual union allselect 'bell' company, 'out' status, 39 eweek, 40 fweek, 1 rnk, 273 cnt from dual union allselect 'bell' company, 'out' status, 40 eweek, 41 fweek, 1 rnk, 145 cnt from dual union allselect 'bell' company, 'out' status, 41 eweek, 42 fweek, 1 rnk, 61 cnt from dual union allselect 'bell' company, 'out' status, 42 eweek, 43 fweek, 1 rnk, 28 cnt from dual union allselect 'bell' company, 'out' status, 27 eweek, NULL fweek, -99999 rnk, 4 cnt from dual union allselect 'bell' company, 'out' status, 28 eweek, NULL fweek, -99999 rnk, 8 cnt from dual)i want to pivot the data to transform columns into row. my pivot query is working ok but i want to add an additional column which is not part of the pivoting.for example, i want to display the following outputCOMPANY STATUS RNK BILL1_VALUE BILL2_VALUE BILL3_VALUE BILL4_VALUE BILL5_VALUE BILL6_VALUE bell out 0 6 71 67 30 40 54 bell out 1 146 256 278 298 277 BILL7_VALUE BILL8_VALUE BILL9_VALUE BILL10_VALUE BILL11_VALUE BILL12_VALUE 63 121 118 294 270 253345 335 261 81 124 82BILL13_VALUE BILL14_VALUE BILL15_VALUE BILL16_VALUE BILL17_VALUE NULL_VALUE 362 273 145 61 28 4 104 83 16 11 2 8If you notice the last column(null_value), i want to include it in my query but dont know how to do it since i am doing pivoting.if i include it as part of pivot, my output is mess up.in addition to null_value column, i want to add the total so my final output should be as follow:COMPANY STATUS RNK BILL1_VALUE BILL2_VALUE BILL3_VALUE BILL4_VALUE BILL5_VALUE BILL6_VALUE bell out 0 6 71 67 30 40 54 bell out 1 146 256 278 298 277 Total 6 217 323 308 338 331  BILL7_VALUE BILL8_VALUE BILL9_VALUE63 121 118 345 335 261408 456 379BILL10_VALUE BILL11_VALUE BILL12_VALUE BILL13_VALUE BILL14_VALUE BILL15_VALUE 294 270 253 362 273 145 81 124 82 104 83 16 375 394 365 466 356 161 BILL16_VALUE BILL17_VALUE NULL_VALUE TOTAL 61 28 4 2260 11 2 8 2742 72 30 12 4997the total row is a sum of all bill columns (vertically) and the total colum is the sum of all bill columns horizontally. in addition i add null_value column which is not part of my pivot query.my query looks like thisSELECT *FROM (select company,status,fweek,rnk,cnt from DATA where fweek<> -99999)PIVOT ( SUM(CNT) VALUE FOR fweek in ( 27 bill1, 28 bill2, 29 bill3, 30 bill4, 31 bill5, 32 bill6, 33 bill7, 34 bill8, 35 bill9, 36 bill10, 37 bill11, 38 bill12, 39 bill13, 40 bill14, 41 bill15, 42 bill16,43 bill17) )this query will pivot my data but does not have the total row, total column and null_value since i dont know how to incorporate that in my query. i was reading about rollup but not very familiar.can someone help me with my query and produce the output above? i am using oracle 11g. thanks in advance

    You were on the right track.  ROLLUP, CUBE and GROUPING SETS are all ways to do aggregates (e.g., SUM for each fweek) and Super-Aggregates (e.g. SUM for all fweeks) together in one query.  In this particular case, CUBE is more convenient than ROLLUP.  You can use GROUP BY CUBE to get the SUM's, and then use PIVOT not to aggregate, but only to pivot.

    Here's one way:

    WITH    relevant_data    AS(    SELECT  company, status, cnt    ,       NVL (fweek, 999)  AS fweek    ,       CASE                  WHEN  rnk >= 0                THEN  rnk                ELSE  eweek - 27   -- or whatever you need           END               AS rnk   FROM    data),    data_to_pivot    AS(    SELECT    company, status, rnk    ,         NVL (fweek, -1)  AS fweek    ,         SUM (cnt)        AS cnt    FROM      relevant_data    GROUP BY  company, status, CUBE (fweek, rnk))SELECT    *FROM      data_to_pivotPIVOT     (    MIN (cnt)  AS value          FOR  fweek  IN (  27  AS bill1                         ,  28  AS bill2                         ,  29  AS bill3                     --  ,  ...                         ,  43  AS bill17                         , 999  AS nll  -- NULL is not a good column name                         ,  -1  AS total                         )          )ORDER BY  company, status, rnk;

    In the query you posted, rows with rnk < 0 were ignored.  You don't want to ignore those rows: you just want to give them different values for rnk and fweek.  I was just guessing at what those different values were above; you may need to change the first sub-query, relevant_data, according to your requirements.

    The output I get isn't quite what you posted:

                    BILL1  BILL2  BILL3  …   BILL17    NLL  TOTALCOMP STA   RNK _VALUE _VALUE _VALUE  …   _VALUE _VALUE _VALUE---- --- ----- ------ ------ ------  …   ------ ------ ------bell out     0      6     71     67  …        2      4   1077bell out     1           146    256  …       28      8   3890bell out            6    217    323  …       30     12   4967

    If you really want the results you posted, explain how you get them.  It would be much easier to read and understand the output if you only used a few values of fweek.

    mNem

Answers

  • mathguy
    mathguy Member Posts: 10,693 Blue Diamond
    edited Nov 8, 2018 1:16AM

    Please explain the desired result for the NULL_VALUE column. There are two rows in the output (ignoring the TOTAL row), for RNK = 0 and RNK = 1. However, both input rows where FWEEK is NULL both have RNK = -99999. So, how do you differentiate between them? Why does the value 4 end up in the row where RNK = 0, and the value 8 in the row where RNK = 1?

    Other than that, the problem is relatively easy. You can indeed use GROUP BY ROLLUP - in a query in which you do all the pivoting "the old way", meaning with conditional aggregation rather than using the PIVOT operator. I can show you how, but I need to understand the answer to the question I asked in the first paragraph.

  • mathguy
    mathguy Member Posts: 10,693 Blue Diamond
    edited Nov 8, 2018 1:31AM

    There are also various other mistakes in your post. The total of all CNT in the DATA rows is 4967, how do you get a total of 4997? Also, the totals for RNK = 0 and RNK = 1 (assuming the 4 and 8 for NULL RNK) are 1077 and 3890 - where did you get your values from? (I haven't run the whole pivoted result set to see exactly where your numbers are wrong, but the totals are surely wrong.)

    In the data you have NULL for FWEEK and -99999 for RNK, but in your attempted query you filter on FWEEK <> -99999.  That makes no sense (it does indeed filter out NULL FWEEK, but that was what you intended to do, it would be a lot clearer to say "where FWEEK is not NULL").

    In any case, getting the NULL_VALUE column is trivial, once you explain how each of the two rows must be assigned to RNK = 0 and 1, respectively (using PIVOT and IGNORING your need for a total column and a total row): you can add NULL to the IN list of the PIVOT operator. This may be surprising (NULL in an IN list shouldn't work), but keep in mind that here the IN clause is not a Boolean condition; rather, it is part of an aggregation, in which all rows where the "relevant column" is NULL are considered a single group (for aggregation purposes). So this is not the hard part; the hard part is to get the totals. As I said, in my opinion the best way to do that is with conditional aggregation.

    Another question for you: it seems the CNT column is already the result of a prior aggregation (you must be counting something there). If that is so, and if you don't need this intermediate result (which is your DATA rowset) for other purposes, you would benefit more if you would show us the real "starting point" for this processing. It makes no sense to aggregate once, then aggregate again. You may be able to get everything you need, with less effort, faster, and with less code (meaning easier maintenance in the future) if you do everything in one pass over the "starting point" data.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,510 Red Diamond
    edited Nov 8, 2018 1:39AM Answer ✓

    Hi,

    elmasduro wrote:hi all,consider the following dataWITH DATA AS (select 'bell' company, 'out' status, 27 eweek, 27 fweek, 0 rnk, 6 cnt from dual union allselect 'bell' company, 'out' status, 28 eweek, 28 fweek, 0 rnk, 71 cnt from dual union allselect 'bell' company, 'out' status, 29 eweek, 29 fweek, 0 rnk, 67 cnt from dual union allselect 'bell' company, 'out' status, 30 eweek, 30 fweek, 0 rnk, 30 cnt from dual union allselect 'bell' company, 'out' status, 31 eweek, 31 fweek, 0 rnk, 40 cnt from dual union allselect 'bell' company, 'out' status, 32 eweek, 32 fweek, 0 rnk, 54 cnt from dual union allselect 'bell' company, 'out' status, 33 eweek, 33 fweek, 0 rnk, 63 cnt from dual union allselect 'bell' company, 'out' status, 34 eweek, 34 fweek, 0 rnk, 121 cnt from dual union allselect 'bell' company, 'out' status, 35 eweek, 35 fweek, 0 rnk, 118 cnt from dual union allselect 'bell' company, 'out' status, 36 eweek, 36 fweek, 0 rnk, 81 cnt from dual union allselect 'bell' company, 'out' status, 37 eweek, 37 fweek, 0 rnk, 124 cnt from dual union allselect 'bell' company, 'out' status, 38 eweek, 38 fweek, 0 rnk, 82 cnt from dual union allselect 'bell' company, 'out' status, 39 eweek, 39 fweek, 0 rnk, 104 cnt from dual union allselect 'bell' company, 'out' status, 40 eweek, 40 fweek, 0 rnk, 83 cnt from dual union allselect 'bell' company, 'out' status, 41 eweek, 41 fweek, 0 rnk, 16 cnt from dual union allselect 'bell' company, 'out' status, 42 eweek, 42 fweek, 0 rnk, 11 cnt from dual union allselect 'bell' company, 'out' status, 43 eweek, 43 fweek, 0 rnk, 2 cnt from dual union allselect 'bell' company, 'out' status, 27 eweek, 28 fweek, 1 rnk, 146 cnt from dual union allselect 'bell' company, 'out' status, 28 eweek, 29 fweek, 1 rnk, 256 cnt from dual union allselect 'bell' company, 'out' status, 29 eweek, 30 fweek, 1 rnk, 278 cnt from dual union allselect 'bell' company, 'out' status, 30 eweek, 31 fweek, 1 rnk, 298 cnt from dual union allselect 'bell' company, 'out' status, 31 eweek, 32 fweek, 1 rnk, 277 cnt from dual union allselect 'bell' company, 'out' status, 32 eweek, 33 fweek, 1 rnk, 345 cnt from dual union allselect 'bell' company, 'out' status, 33 eweek, 34 fweek, 1 rnk, 335 cnt from dual union allselect 'bell' company, 'out' status, 34 eweek, 35 fweek, 1 rnk, 261 cnt from dual union allselect 'bell' company, 'out' status, 35 eweek, 36 fweek, 1 rnk, 294 cnt from dual union allselect 'bell' company, 'out' status, 36 eweek, 37 fweek, 1 rnk, 270 cnt from dual union allselect 'bell' company, 'out' status, 37 eweek, 38 fweek, 1 rnk, 253 cnt from dual union allselect 'bell' company, 'out' status, 38 eweek, 39 fweek, 1 rnk, 362 cnt from dual union allselect 'bell' company, 'out' status, 39 eweek, 40 fweek, 1 rnk, 273 cnt from dual union allselect 'bell' company, 'out' status, 40 eweek, 41 fweek, 1 rnk, 145 cnt from dual union allselect 'bell' company, 'out' status, 41 eweek, 42 fweek, 1 rnk, 61 cnt from dual union allselect 'bell' company, 'out' status, 42 eweek, 43 fweek, 1 rnk, 28 cnt from dual union allselect 'bell' company, 'out' status, 27 eweek, NULL fweek, -99999 rnk, 4 cnt from dual union allselect 'bell' company, 'out' status, 28 eweek, NULL fweek, -99999 rnk, 8 cnt from dual)i want to pivot the data to transform columns into row. my pivot query is working ok but i want to add an additional column which is not part of the pivoting.for example, i want to display the following outputCOMPANY STATUS RNK BILL1_VALUE BILL2_VALUE BILL3_VALUE BILL4_VALUE BILL5_VALUE BILL6_VALUE bell out 0 6 71 67 30 40 54 bell out 1 146 256 278 298 277 BILL7_VALUE BILL8_VALUE BILL9_VALUE BILL10_VALUE BILL11_VALUE BILL12_VALUE 63 121 118 294 270 253345 335 261 81 124 82BILL13_VALUE BILL14_VALUE BILL15_VALUE BILL16_VALUE BILL17_VALUE NULL_VALUE 362 273 145 61 28 4 104 83 16 11 2 8If you notice the last column(null_value), i want to include it in my query but dont know how to do it since i am doing pivoting.if i include it as part of pivot, my output is mess up.in addition to null_value column, i want to add the total so my final output should be as follow:COMPANY STATUS RNK BILL1_VALUE BILL2_VALUE BILL3_VALUE BILL4_VALUE BILL5_VALUE BILL6_VALUE bell out 0 6 71 67 30 40 54 bell out 1 146 256 278 298 277 Total 6 217 323 308 338 331  BILL7_VALUE BILL8_VALUE BILL9_VALUE63 121 118 345 335 261408 456 379BILL10_VALUE BILL11_VALUE BILL12_VALUE BILL13_VALUE BILL14_VALUE BILL15_VALUE 294 270 253 362 273 145 81 124 82 104 83 16 375 394 365 466 356 161 BILL16_VALUE BILL17_VALUE NULL_VALUE TOTAL 61 28 4 2260 11 2 8 2742 72 30 12 4997the total row is a sum of all bill columns (vertically) and the total colum is the sum of all bill columns horizontally. in addition i add null_value column which is not part of my pivot query.my query looks like thisSELECT *FROM (select company,status,fweek,rnk,cnt from DATA where fweek<> -99999)PIVOT ( SUM(CNT) VALUE FOR fweek in ( 27 bill1, 28 bill2, 29 bill3, 30 bill4, 31 bill5, 32 bill6, 33 bill7, 34 bill8, 35 bill9, 36 bill10, 37 bill11, 38 bill12, 39 bill13, 40 bill14, 41 bill15, 42 bill16,43 bill17) )this query will pivot my data but does not have the total row, total column and null_value since i dont know how to incorporate that in my query. i was reading about rollup but not very familiar.can someone help me with my query and produce the output above? i am using oracle 11g. thanks in advance

    You were on the right track.  ROLLUP, CUBE and GROUPING SETS are all ways to do aggregates (e.g., SUM for each fweek) and Super-Aggregates (e.g. SUM for all fweeks) together in one query.  In this particular case, CUBE is more convenient than ROLLUP.  You can use GROUP BY CUBE to get the SUM's, and then use PIVOT not to aggregate, but only to pivot.

    Here's one way:

    WITH    relevant_data    AS(    SELECT  company, status, cnt    ,       NVL (fweek, 999)  AS fweek    ,       CASE                  WHEN  rnk >= 0                THEN  rnk                ELSE  eweek - 27   -- or whatever you need           END               AS rnk   FROM    data),    data_to_pivot    AS(    SELECT    company, status, rnk    ,         NVL (fweek, -1)  AS fweek    ,         SUM (cnt)        AS cnt    FROM      relevant_data    GROUP BY  company, status, CUBE (fweek, rnk))SELECT    *FROM      data_to_pivotPIVOT     (    MIN (cnt)  AS value          FOR  fweek  IN (  27  AS bill1                         ,  28  AS bill2                         ,  29  AS bill3                     --  ,  ...                         ,  43  AS bill17                         , 999  AS nll  -- NULL is not a good column name                         ,  -1  AS total                         )          )ORDER BY  company, status, rnk;

    In the query you posted, rows with rnk < 0 were ignored.  You don't want to ignore those rows: you just want to give them different values for rnk and fweek.  I was just guessing at what those different values were above; you may need to change the first sub-query, relevant_data, according to your requirements.

    The output I get isn't quite what you posted:

                    BILL1  BILL2  BILL3  …   BILL17    NLL  TOTALCOMP STA   RNK _VALUE _VALUE _VALUE  …   _VALUE _VALUE _VALUE---- --- ----- ------ ------ ------  …   ------ ------ ------bell out     0      6     71     67  …        2      4   1077bell out     1           146    256  …       28      8   3890bell out            6    217    323  …       30     12   4967

    If you really want the results you posted, explain how you get them.  It would be much easier to read and understand the output if you only used a few values of fweek.

    mNem
  • elmasduro
    elmasduro Member Posts: 424
    edited Nov 8, 2018 7:22AM

    hi Frank, thank you for your query.  yes, the null_value column is the result of those rows with rnk = -99999. you have correct in your output

    the total column should be equal to: bill1_value + bill2_value + bill3_value ... + bill17_value   going horizontal for each row.

    maybe i  made a mistake on my math by i will verify your output and redo the math to see if i can match your values

    also, the last row in your output which is summing up the values of all rows for each column is correct.

    the only thing is that ii would like value Total instead of Bell for the company column for the last row.

    how can i do that?

    thanks in advance

  • mathguy
    mathguy Member Posts: 10,693 Blue Diamond
    edited Nov 8, 2018 7:50AM

    Will you answer the question in Reply 1?

    For what it's worth, I believe the answer in Reply 3 does more work than is necessary. If you need to aggregate in a standard aggregate query (with GROUP BY), and then you need to PIVOT the results (a second aggregation), you will save time and resources by doing everything in the aggregate (GROUP BY) query so that you don't need a second aggregation.

    Of course, you can simply choose to work with Mr. Kulash, answer his questions and ignore mine, and go with that solution. Your privilege. You would not be the first one to do that.

  • Etbin
    Etbin Member Posts: 8,968 Gold Crown
    edited Nov 8, 2018 8:10AM

    Maybe (altering rnk where fweek is null)

    with

    data as

    (select 'bell' company,'out' status,27 eweek,27 fweek,0 rnk,6 cnt from dual union all

    select 'bell','out',28,28,0,71 from dual union all

    select 'bell','out',29,29,0,67 from dual union all

    select 'bell','out',30,30,0,30 from dual union all

    select 'bell','out',31,31,0,40 from dual union all

    select 'bell','out',32,32,0,54 from dual union all

    select 'bell','out',33,33,0,63 from dual union all

    select 'bell','out',34,34,0,121 from dual union all

    select 'bell','out',35,35,0,118 from dual union all

    select 'bell','out',36,36,0,81 from dual union all

    select 'bell','out',37,37,0,124 from dual union all

    select 'bell','out',38,38,0,82 from dual union all

    select 'bell','out',39,39,0,104 from dual union all

    select 'bell','out',40,40,0,83 from dual union all

    select 'bell','out',41,41,0,16 from dual union all

    select 'bell','out',42,42,0,11 from dual union all

    select 'bell','out',43,43,0,2 from dual union all

    select 'bell','out',27,28,1,146 from dual union all

    select 'bell','out',28,29,1,256 from dual union all

    select 'bell','out',29,30,1,278 from dual union all

    select 'bell','out',30,31,1,298 from dual union all

    select 'bell','out',31,32,1,277 from dual union all

    select 'bell','out',32,33,1,345 from dual union all

    select 'bell','out',33,34,1,335 from dual union all

    select 'bell','out',34,35,1,261 from dual union all

    select 'bell','out',35,36,1,294 from dual union all

    select 'bell','out',36,37,1,270 from dual union all

    select 'bell','out',37,38,1,253 from dual union all

    select 'bell','out',38,39,1,362 from dual union all

    select 'bell','out',39,40,1,273 from dual union all

    select 'bell','out',40,41,1,145 from dual union all

    select 'bell','out',41,42,1,61 from dual union all

    select 'bell','out',42,43,1,28 from dual union all

    select 'bell','out',27,NULL,0,4 from dual union all

    select 'bell','out',28,NULL,1,8 from dual

    )

    select nvl(company,'Total') company,status,rnk,bill1_value,bill2_value,bill3_value,bill4_value,bill5_value,bill6_value,

           bill7_value,bill8_value,bill9_value,bill10_value,bill11_value,bill12_value,bill13_value,bill14_value,bill15_value,

           bill16_value,bill17_value,null_value,total

      from (select company,status,rnk,sum(bill1_value) bill1_value,sum(bill2_value) bill2_value,sum(bill3_value) bill3_value,

                   sum(bill4_value) bill4_value,sum(bill5_value) bill5_value,sum(bill6_value) bill6_value,

                   sum(bill7_value) bill7_value,sum(bill8_value) bill8_value,sum(bill9_value) bill9_value,

                   sum(bill10_value) bill10_value,sum(bill11_value) bill11_value,sum(bill12_value) bill12_value,

                   sum(bill13_value) bill13_value,sum(bill14_value) bill14_value,sum(bill15_value) bill15_value,

                   sum(bill16_value) bill16_value,sum(bill17_value) bill17_value,

                   sum(null_value) null_value,sum(total) total

              from (select company,status,rnk,bill1_value,bill2_value,bill3_value,bill4_value,bill5_value,bill6_value,bill7_value,

                           bill8_value,bill9_value,bill10_value,bill11_value,bill12_value,bill13_value,bill14_value,bill15_value,

                           bill16_value,bill17_value,null_value,total

                      from (select company,status,eweek,fweek,rnk,cnt,

                                   sum(case when fweek is null then cnt end) over (partition by company,status,eweek,fweek,rnk) null_value,

                                   sum(cnt) over (partition by company,status,eweek,fweek,rnk) total

                              from data

                           )

                     pivot (sum(cnt) value for fweek in (27 bill1,28 bill2,29 bill3,30 bill4,31 bill5,32 bill6,33 bill7,34 bill8,

                                                         35 bill9,36 bill10,37 bill11,38 bill12,39 bill13,40 bill14,41 bill15,

                                                         42 bill16,43 bill17

                                                        )

                            )

                    )

             group by rollup(company,status,rnk)

            )

    where company is not null

       and status is not null

       and rnk is not null

        or coalesce(company,status,to_char(rnk)) is null

    COMPANYSTATUSRNKBILL1_VALUEBILL2_VALUEBILL3_VALUEBILL4_VALUEBILL5_VALUEBILL6_VALUEBILL7_VALUEBILL8_VALUEBILL9_VALUEBILL10_VALUEBILL11_VALUEBILL12_VALUEBILL13_VALUEBILL14_VALUEBILL15_VALUEBILL16_VALUEBILL17_VALUENULL_VALUETOTAL
    bellout067167304054631211188112482104831611241077
    bellout1-146256278298277345335261294270253362273145612883890
    Total--62173233083383314084563793753943354663561617230124967

    Regards

    Etbin

  • mathguy
    mathguy Member Posts: 10,693 Blue Diamond
    edited Nov 8, 2018 8:15AM

    Actually, I see you accepted Mr. Kulash's guess: when RNK = -99999, it should be replaced with EWEEK - 27.

    Then you can write the query like this. I show only the BILL1_VALUE, BILL2_VALUE and NULL_VALUE (and TOTAL_VALUE) columns; to get the other BILLn_VALUE columns, you need to add to the final SELECT list in an obvious way.

    WITH DATA AS (select 'bell' company, 'out' status, 27 eweek, 27 fweek, 0 rnk, 6 cnt from dual union allselect 'bell' company, 'out' status, 28 eweek, 28 fweek, 0 rnk, 71 cnt from dual union allselect 'bell' company, 'out' status, 29 eweek, 29 fweek, 0 rnk, 67 cnt from dual union allselect 'bell' company, 'out' status, 30 eweek, 30 fweek, 0 rnk, 30 cnt from dual union allselect 'bell' company, 'out' status, 31 eweek, 31 fweek, 0 rnk, 40 cnt from dual union allselect 'bell' company, 'out' status, 32 eweek, 32 fweek, 0 rnk, 54 cnt from dual union allselect 'bell' company, 'out' status, 33 eweek, 33 fweek, 0 rnk, 63 cnt from dual union allselect 'bell' company, 'out' status, 34 eweek, 34 fweek, 0 rnk, 121 cnt from dual union allselect 'bell' company, 'out' status, 35 eweek, 35 fweek, 0 rnk, 118 cnt from dual union allselect 'bell' company, 'out' status, 36 eweek, 36 fweek, 0 rnk, 81 cnt from dual union allselect 'bell' company, 'out' status, 37 eweek, 37 fweek, 0 rnk, 124 cnt from dual union allselect 'bell' company, 'out' status, 38 eweek, 38 fweek, 0 rnk, 82 cnt from dual union allselect 'bell' company, 'out' status, 39 eweek, 39 fweek, 0 rnk, 104 cnt from dual union allselect 'bell' company, 'out' status, 40 eweek, 40 fweek, 0 rnk, 83 cnt from dual union allselect 'bell' company, 'out' status, 41 eweek, 41 fweek, 0 rnk, 16 cnt from dual union allselect 'bell' company, 'out' status, 42 eweek, 42 fweek, 0 rnk, 11 cnt from dual union allselect 'bell' company, 'out' status, 43 eweek, 43 fweek, 0 rnk, 2 cnt from dual union allselect 'bell' company, 'out' status, 27 eweek, 28 fweek, 1 rnk, 146 cnt from dual union allselect 'bell' company, 'out' status, 28 eweek, 29 fweek, 1 rnk, 256 cnt from dual union allselect 'bell' company, 'out' status, 29 eweek, 30 fweek, 1 rnk, 278 cnt from dual union allselect 'bell' company, 'out' status, 30 eweek, 31 fweek, 1 rnk, 298 cnt from dual union allselect 'bell' company, 'out' status, 31 eweek, 32 fweek, 1 rnk, 277 cnt from dual union allselect 'bell' company, 'out' status, 32 eweek, 33 fweek, 1 rnk, 345 cnt from dual union allselect 'bell' company, 'out' status, 33 eweek, 34 fweek, 1 rnk, 335 cnt from dual union allselect 'bell' company, 'out' status, 34 eweek, 35 fweek, 1 rnk, 261 cnt from dual union allselect 'bell' company, 'out' status, 35 eweek, 36 fweek, 1 rnk, 294 cnt from dual union allselect 'bell' company, 'out' status, 36 eweek, 37 fweek, 1 rnk, 270 cnt from dual union allselect 'bell' company, 'out' status, 37 eweek, 38 fweek, 1 rnk, 253 cnt from dual union allselect 'bell' company, 'out' status, 38 eweek, 39 fweek, 1 rnk, 362 cnt from dual union allselect 'bell' company, 'out' status, 39 eweek, 40 fweek, 1 rnk, 273 cnt from dual union allselect 'bell' company, 'out' status, 40 eweek, 41 fweek, 1 rnk, 145 cnt from dual union allselect 'bell' company, 'out' status, 41 eweek, 42 fweek, 1 rnk, 61 cnt from dual union allselect 'bell' company, 'out' status, 42 eweek, 43 fweek, 1 rnk, 28 cnt from dual union allselect 'bell' company, 'out' status, 27 eweek, NULL fweek, -99999 rnk, 4 cnt from dual union allselect 'bell' company, 'out' status, 28 eweek, NULL fweek, -99999 rnk, 8 cnt from dual), prep as (  select company, status, fweek, decode(rnk, -99999, eweek - 27, rnk) as rnk, cnt  from  data)select   case grouping_id(rnk) when 0 then company else 'Total' end as company     ,   case grouping_id(rnk) when 0 then status end as status     ,   rnk     ,   sum(case fweek when 27 then cnt end) as bill1_value     ,   sum(case fweek when 28 then cnt end) as bill2_value  -- ,   sum(case fweek when 29 then cnt end) as bill3_value,   etc.     ,   sum(case when fweek is null then cnt end) as null_value     ,   sum(cnt) as total_valuefrom     prepgroup by company, status, rollup(rnk)order by grouping_id(rnk), rnk;COMPANY STATUS  RNK BILL1_VALUE BILL2_VALUE NULL_VALUE TOTAL_VALUE-----  ------ ---- ----------- ----------- ---------- -----------bell    out      0           6          71          4        1077bell    out      1                     146          8        3890Total                        6         217         12        4967
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,510 Red Diamond
    edited Nov 8, 2018 9:36AM

    Hi,

    elmasduro wrote:...also, the last row in your output which is summing up the values of all rows for each column is correct.the only thing is that ii would like value Total instead of Bell for the company column for the last row.how can i do that? ... 

    There are lots of different ways, depending on your requirements.  What are your requirements?  What does each row of the output represent?  What happens if there are multiple companies and/or statuses?  Answer these, and all the other questions that people trying to help you asked above.

    Point out where the solutions in replies #6 and #7 aren't doing what you need, and explain again how you get the correct results in those places.