how to reduce mysql this query as short as possible? [on hold]

140

December 14, 2018, at 03:10 AM

/*Residential*/
SELECT 
SUM(IF(consbill.volume >=0 AND consbill.volume<=10,1,0)) AS 0010_conn,
SUM(IF(consbill.volume >=0 AND consbill.volume<=10,consbill.volume,0)) AS 0010_vol,
SUM(IF(consbill.volume >=0 AND consbill.volume<=10,consbill.current_bill,0)) AS 0010_amount, 
SUM(IF(consbill.volume >=11 AND consbill.volume<=20,1,0)) AS 1120_conn,
SUM(IF(consbill.volume >=11 AND consbill.volume<=20,consbill.volume,0)) AS 1120_vol,
SUM(IF(consbill.volume >=11 AND consbill.volume<=20,consbill.current_bill,0)) AS 1120_amount, 
SUM(IF(consbill.volume >=21 AND consbill.volume<=30,1,0)) AS 2130_conn,
SUM(IF(consbill.volume >=21 AND consbill.volume<=30,consbill.volume,0)) AS 2130_vol,
SUM(IF(consbill.volume >=21 AND consbill.volume<=30,consbill.current_bill,0)) AS 2130_amount,
SUM(IF(consbill.volume >=31 AND consbill.volume<=40,1,0)) AS 3140_conn,
SUM(IF(consbill.volume >=31 AND consbill.volume<=40,consbill.volume,0)) AS 3140_vol,
SUM(IF(consbill.volume >=31 AND consbill.volume<=40,consbill.current_bill,0)) AS 3140_amount,
SUM(IF(consbill.volume >=41 AND consbill.volume<=50,1,0)) AS 4150_conn,
SUM(IF(consbill.volume >=41 AND consbill.volume<=50,consbill.volume,0)) AS 4150_vol,
SUM(IF(consbill.volume >=41 AND consbill.volume<=50,consbill.current_bill,0)) AS 4150_amount,
SUM(IF(consbill.volume >=51 AND consbill.volume<=100,1,0)) AS 51100_conn,
SUM(IF(consbill.volume >=51 AND consbill.volume<=100,consbill.volume,0)) AS 51100_vol,
SUM(IF(consbill.volume >=51 AND consbill.volume<=100,consbill.current_bill,0)) AS 51100_amount,
SUM(IF(consbill.volume >=101 AND consbill.volume<=200,1,0)) AS 101200_conn,
SUM(IF(consbill.volume >=101 AND consbill.volume<=200,consbill.volume,0)) AS 101200_vol,
SUM(IF(consbill.volume >=101 AND consbill.volume<=200,consbill.current_bill,0)) AS 101200_amount     
FROM consumer_billing consbill, consumers cons, consumer_category conscat 
WHERE consbill.cons_ctrl = cons.cons_ctrl AND consbill.zone_code = '201'
AND consbill.billmonth = '01-2018' AND cons.category_code >= 12 AND cons.category_code >= 13 
AND cons.category_code >= 14 UNION ALL
/*commercial */
SELECT 
SUM(IF(consbill.volume >=0 AND consbill.volume<=10,1,0)) AS 0010_conn,
SUM(IF(consbill.volume >=0 AND consbill.volume<=10,consbill.volume,0)) AS 0010_vol,
SUM(IF(consbill.volume >=0 AND consbill.volume<=10,consbill.current_bill,0)) AS 0010_amount, 
SUM(IF(consbill.volume >=11 AND consbill.volume<=20,1,0)) AS 1120_conn,
SUM(IF(consbill.volume >=11 AND consbill.volume<=20,consbill.volume,0)) AS 1120_vol,
SUM(IF(consbill.volume >=11 AND consbill.volume<=20,consbill.current_bill,0)) AS 1120_amount, 
SUM(IF(consbill.volume >=21 AND consbill.volume<=30,1,0)) AS 2130_conn,
SUM(IF(consbill.volume >=21 AND consbill.volume<=30,consbill.volume,0)) AS 2130_vol,
SUM(IF(consbill.volume >=21 AND consbill.volume<=30,consbill.current_bill,0)) AS 2130_amount,
SUM(IF(consbill.volume >=31 AND consbill.volume<=40,1,0)) AS 3140_conn,
SUM(IF(consbill.volume >=31 AND consbill.volume<=40,consbill.volume,0)) AS 3140_vol,
SUM(IF(consbill.volume >=31 AND consbill.volume<=40,consbill.current_bill,0)) AS 3140_amount,
SUM(IF(consbill.volume >=41 AND consbill.volume<=50,1,0)) AS 4150_conn,
SUM(IF(consbill.volume >=41 AND consbill.volume<=50,consbill.volume,0)) AS 4150_vol,
SUM(IF(consbill.volume >=41 AND consbill.volume<=50,consbill.current_bill,0)) AS 4150_amount,
SUM(IF(consbill.volume >=51 AND consbill.volume<=100,1,0)) AS 51100_conn,
SUM(IF(consbill.volume >=51 AND consbill.volume<=100,consbill.volume,0)) AS 51100_vol,
SUM(IF(consbill.volume >=51 AND consbill.volume<=100,consbill.current_bill,0)) AS 51100_amount,
SUM(IF(consbill.volume >=101 AND consbill.volume<=200,1,0)) AS 101200_conn,
SUM(IF(consbill.volume >=101 AND consbill.volume<=200,consbill.volume,0)) AS 101200_vol,
SUM(IF(consbill.volume >=101 AND consbill.volume<=200,consbill.current_bill,0)) AS 101200_amount     
FROM consumer_billing consbill, consumers cons, consumer_category conscat 
WHERE consbill.cons_ctrl = cons.cons_ctrl AND consbill.zone_code = '201'
AND consbill.billmonth = '01-2018' AND cons.category_code >= 22 AND cons.category_code >= 23 
AND cons.category_code >= 24

POPULAR ONLINE

angularjs controller and directive data binding not working

PDO: How to Insert Multiple Rows in MySQL with one process

E2E tests timeout when running against localhost

ReactJS: How do I animate nested elements inside a .map()?

How different are PHP and Node applications performances? [closed]

SQL Replace Join With Something Faster [on hold]

I have a view in SQL that uses a join and takes a lot longer than I would like for it to takeI think it would run much faster if I converted it to a subquery instead, but I'm having trouble with that

120

MySQL

How to write the query without using sub query?

I have this query and it works perfectly but I wanted to improve it and write it without sub-queryIs this possible? Although I have tried a lot, I am not able to get correct results

115

MySQL

mysql join only if a field has a particular value

I am trying to fetch a table on certain conditions with joinMy table is:

217

$“Call to undefined method Illuminate\Support\Facades\File::save()”$

MySQL

“Call to undefined method Illuminate\Support\Facades\File::save()”

I want stored data in database with image pathI write following function but following error occur

197