SQL Query - Pivot with Ranges 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&gt; &lt;= 20    &gt;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&gt; &lt;= 20   &gt;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 &gt; '20' 
group by Bseg_ID, BILL_SQ) PIVOT (Count (BSEG_ID) for BILL_SQ IN ('0-5'));
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` &gt; 10 AND `BILL_SQ` &lt;= 20) '10&gt; &lt;= 20',
    SUM(`BILL_SQ` &gt; 20) '&gt;20'
FROM CI_BSEG_CALC_LN
GROUP BY `BSEG_ID`, `DST_ID`


BSEG_ID | DST_ID   | 0-10 | 10&gt; &lt;= 20 | &gt;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` &gt; 10 AND `res_sum` &lt;= 20) '10&gt; &lt;= 20',
    SUM(`res_sum` &gt; 20) '&gt;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&gt; &lt;= 20 | &gt;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&lt;&gt;fiddle here
For ORACLE


SELECT
    BSEG_ID,DST_ID,
     SUM(CASE WHEN BILL_SQ &gt;= 0 AND BILL_SQ &lt;= 10 THEN 1 ELSE 0 END) &quot;0-10&quot;,
     SUM(CASE WHEN BILL_SQ &gt; 10 AND BILL_SQ &lt;= 20 THEN 1 ELSE 0 END) &quot;10&gt; &lt;= 20&quot;,
    SUM(CASE WHEN BILL_SQ &gt; 20 THEN 1 ELSE 0 END) &quot;&gt;20&quot;
    
FROM CI_BSEG_CALC_LN
GROUP BY BSEG_ID, DST_ID


BSEG_ID | DST_ID   | 0-10 | 10&gt; &lt;= 20 | &gt;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 &gt;= 0 AND res_sum &lt;= 10 THEN 1 ELSE 0 END) &quot;0-10&quot;,
     SUM(CASE WHEN res_sum &gt; 10 AND res_sum &lt;= 20 THEN 1 ELSE 0 END) &quot;10&gt; &lt;= 20&quot;,
    SUM(CASE WHEN res_sum &gt; 20 THEN 1 ELSE 0 END) &quot;&gt;20&quot;
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&gt; &lt;= 20 | &gt;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&lt;&gt;fiddle here