0

This is my sample table

BSEG_ID DST_ID  BILL_SQ AUDIT_CALC_AMT  BILL_SQ, MSR_PEAK_QTY_SW, DESCR_ON_BILL, VERSION, AUDIT_CALC_AMT
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 #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

Expected Output #2 Range for Sum of AUDIT_CALC_AMT

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 my current query:

select a.bseg_id,a.dst_id, sum (a.bill_sq) as WaterConsumption ,sum (a.AUDIT_CALC_AMT) as SWConsumption from CI_BSEG_CALC_LN a 
--where a. BSEG_ID = '209875510608'
AND a.DST_ID in ('REV-WSC','REV-WWSC')
group by a.bseg_id, a.dst_id;

Please suggest a way for achieving the desired output.

This is what I have done so far

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'));
0
1

In Mysql You can use SUM with an comparision, which returns 1 if the condition uis met or 0 if not.

SELECT
    `BSEG_ID`, `DST_ID`,
    SUM(`BILL_SQ` BETWEEN 0 AND 10) '0-10',
    SUM(`BILL_SQ` > 10 AND `BILL_SQ` <= 20) '10> <= 20',
    SUM(`BILL_SQ` > 20) '>20'
FROM CI_BSEG_CALC_LN
GROUP BY `BSEG_ID`, `DST_ID`
BSEG_ID | DST_ID   | 0-10 | 10> <= 20 | >20
------: | :------- | ---: | --------: | --:
  11111 | REV-WSC  |    1 |         0 |   2
  11111 | REV_WWSC |    1 |         0 |   2
  12334 | REV-WSC  |    1 |         0 |   2
  12334 | REV_WWSC |    1 |         0 |   2
SELECT
    `BSEG_ID`, `DST_ID`,
    SUM(`res_sum` BETWEEN 0 AND 10) '0-10',
    SUM(`res_sum` > 10 AND `res_sum` <= 20) '10> <= 20',
    SUM(`res_sum` > 20) '>20'
FROM
    (SELECT
         `BSEG_ID`, `DST_ID`,
         SUM(`AUDIT_CALC_AMT`) res_sum
     FROM CI_BSEG_CALC_LN
     GROUP BY `BSEG_ID`, `DST_ID`) t1
GROUP BY `BSEG_ID`, `DST_ID`
BSEG_ID | DST_ID   | 0-10 | 10> <= 20 | >20
------: | :------- | ---: | --------: | --:
  11111 | REV-WSC  |    0 |         0 |   1
  11111 | REV_WWSC |    0 |         0 |   1
  12334 | REV-WSC  |    0 |         0 |   1
  12334 | REV_WWSC |    0 |         0 |   1

db<>fiddle here

For ORACLE

SELECT
    BSEG_ID,DST_ID,
     SUM(CASE WHEN BILL_SQ >= 0 AND BILL_SQ <= 10 THEN 1 ELSE 0 END) "0-10",
     SUM(CASE WHEN BILL_SQ > 10 AND BILL_SQ <= 20 THEN 1 ELSE 0 END) "10> <= 20",
    SUM(CASE WHEN BILL_SQ > 20 THEN 1 ELSE 0 END) ">20"
    
FROM CI_BSEG_CALC_LN
GROUP BY BSEG_ID, DST_ID
BSEG_ID | DST_ID   | 0-10 | 10> <= 20 | >20
------: | :------- | ---: | --------: | --:
  12334 | REV-WSC  |    1 |         0 |   2
  12334 | REV_WWSC |    1 |         0 |   2
  11111 | REV_WWSC |    1 |         0 |   2
  11111 | REV-WSC  |    1 |         0 |   2
SELECT
    BSEG_ID,DST_ID,
     SUM(CASE WHEN res_sum >= 0 AND res_sum <= 10 THEN 1 ELSE 0 END) "0-10",
     SUM(CASE WHEN res_sum > 10 AND res_sum <= 20 THEN 1 ELSE 0 END) "10> <= 20",
    SUM(CASE WHEN res_sum > 20 THEN 1 ELSE 0 END) ">20"
FROM
    (SELECT
         BSEG_ID,DST_ID,
         SUM(AUDIT_CALC_AMT) res_sum
     FROM CI_BSEG_CALC_LN
     GROUP BY BSEG_ID, DST_ID) t1
GROUP BY BSEG_ID, DST_ID
BSEG_ID | DST_ID   | 0-10 | 10> <= 20 | >20
------: | :------- | ---: | --------: | --:
  12334 | REV-WSC  |    0 |         0 |   1
  12334 | REV_WWSC |    0 |         0 |   1
  11111 | REV_WWSC |    0 |         0 |   1
  11111 | REV-WSC  |    0 |         0 |   1

db<>fiddle here

4
  • thanks nbk. However, have slightly tailored your query as it is giving error on PLSQL.SELECT BSEG_ID, DST_ID, SUM(BILL_SQ BETWEEN 0 AND 10) "0-10", SUM(BILL_SQ > 10 AND BILL_SQ <= 20) "10> <= 20", SUM(BILL_SQ > 20) ">20" FROM CI_BSEG_CALC_LN GROUP BY BSEG_ID, DST_ID; this is slightly tailored query but still i ma getting following error "ORA-00907: missing right parenthesis"
    – Nabeel
    Oct 5 '20 at 11:02
  • you should have tagged the question with oracle and version you used.
    – nbk
    Oct 5 '20 at 11:04
  • Apologies ..i am using plsql developer 4.1
    – Nabeel
    Oct 5 '20 at 11:07
  • @Nabeel i added an Oracle Version, it is basically the same, only without shortccuts
    – nbk
    Oct 5 '20 at 11:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.