Post reply

Pivot with Ranges

  • October 5, 2020 at 9:40 am

    #3792807

    This is my table

    BSEG_ID    DST_ID      BILL_SQ        AUDIT_CALC_AMT                Remarks

    12334           REV-WSC      62                   6.40667                                For 0 - 60 (Next 62.000 cubic meter) @US0.10 per cubic meter

    12334           REV-WSC      62                  64.06667                               For 60 - 120 (Next 62.000 cubic meter) @US1.00 per cubic meter

    12334           REV-WSC      3.6                 11.16                                       For 120 - 180 (Next 3.600 cubic meter) @US3.00 per cubic meter

    12334           REV_WWSC 62                  3.20333                                 For 0 - 60 (Next 62.000 cubic meter) @US0.05 per cubic meter

    12334           REV_WWSC 62                  32.03333                             For 60 - 120 (Next 62.000 cubic meter) @USSAR0.50 per cubic meter

    12334           REV_WWSC 3.6                   5.58                                      For 120 - 180 (Next 3.600 cubic meter) @US1.50 per cubic meter

    11111            REV-WSC       62                   6.40667                                For 0 - 60 (Next 62.000 cubic meter) @US0.10 per cubic meter

    11111            REV-WSC       62                    64.06667                            For 60 - 120 (Next 62.000 cubic meter) @US1.00 per cubic meter

    11111            REV-WSC      3.6                    11.16                                     For 120 - 180 (Next 3.600 cubic meter) @US3.00 per cubic meter

    11111            REV_WWSC 62                      3.20333                             For 0 - 60 (Next 62.000 cubic meter) @US0.05 per cubic meter

    11111           REV_WWSC  62                      32.03333                        For 60 - 120 (Next 62.000 cubic meter) @USSAR0.50 per cubic meter

    11111           REV_WWSC  3.6                     5.58                                For 120 - 180 (Next 3.600 cubic meter) @US1.50 per cubic meter

    Expected Output

    Expected Output #1 Range for Bill_SQ

    BSEG_ID       DST_ID                      0-10            10> <= 20         >20

    11111               REV_WWSC                0                         0                    1

    11111               REV-WSC                     0                         0                    1

    12334             REV_WWSC               0                          0                    1

    12334             REV-WSC                    0                          0                    1

    This is what i have done so far but is giving an error

     

    select * from ( select  Bseg_ID , BILL_SQ, count (*)

    from CI_BSEG_CALC_LN

    where BILL_SQ Between '0' AND '5'

    --AND BILL_SQ Between '6' AND '10'

    --AND   BILL_SQ Between '11' AND '15'

    --AND   BILL_SQ Between '16' AND '20'

    --AND   BILL_SQ > '20'

    group by Bseg_ID, BILL_SQ)

    PIVOT (Count (BSEG_ID) for BILL_SQ  IN ('0-5'));

    Please suggest a way to achieve desired output....

     

  • October 5, 2020 at 11:08 am

    #3792817

    i am using plsql developer 4.1

  • October 5, 2020 at 1:05 pm

    #3792858

    Do the remarks mean anything?  I ask because they appear to be incorrect for the ranges you're talking about both in the  BILL_SQ and the AUDIT_CALC_AMT columns.  It would be nice if you took a bit of time to make the expected output match the inputs.  It would also help you get an answer more quickly if you provided the example data in a readily consumable format (see the article at the first link in my signature line below for one way to do that and why).

    Also, since you're using PLSQL, be advised that answers provided on this forum may not work in Oracle because PLSQL <> T-SQL.

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply