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
Rent Charter Buses Company
READ ALSO
SQL Replace Join With Something Faster [on hold]

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
How to write the query without using sub query?

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 join only if a field has a particular value

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
&ldquo;Call to undefined method Illuminate\Support\Facades\File::save()&rdquo;

“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