Mysql select query with many condition fields

152
December 03, 2017, at 5:02 PM

I have a table like this:

my index is id_station_date_hour
My query:

SELECT id_station,area_type,
SUM(IF(goinside = 1 AND(zone1+zone2+zone3+cashiertime+special) > 0 AND DATE >= '2017-10-01' AND DATE <= '2017-10-31' AND HOUR >= 9 AND HOUR < 22, zone1+zone2+zone3+cashiertime+special, 0) AS ex_z1z2z3z4z5_time,
SUM(IF(goinside = 1 AND(zone1+zone2+zone3+cashiertime+special) > 0 AND DATE >= '2017-10-01' AND DATE <= '2017-10-31' AND HOUR >= 9 AND HOUR < 22, 1 , 0) AS ex_z1z2z3z4z5_num,
SUM(IF(goinside = 1 AND(zone1+zone2+zone3+cashiertime+special) > 0 AND DATE >= '2017-08-31' AND DATE <= '2017-09-30' AND HOUR >= 9 AND HOUR < 22, zone1+zone2+zone3+cashiertime+special , 0) AS ex_z1z2z3z4z5_time_prev,
SUM(IF(goinside = 1 AND(zone1+zone2+zone3+cashiertime+special) > 0 AND DATE >= '2017-08-31' AND DATE <= '2017-09-30' AND HOUR >= 9 AND HOUR < 22, 1 , 0) AS ex_z1z2z3z4z5_num_prev,
SUM(IF(goinside = 1 AND (zone1+zone2+zone3+cashiertime+special) >= 180 AND (zone1+zone2+zone3+cashiertime+special) < 360 AND DATE >= '2017-10-01' AND DATE <= '2017-10-31' AND HOUR >= 9 AND HOUR < 22, zone1+zone2+zone3+cashiertime+special, 0) AS ex_z1z2z3z4z5_num_3to6,
SUM(IF(goinside = 1 AND (zone1+zone2+zone3+cashiertime+special) >= 180 AND (zone1+zone2+zone3+cashiertime+special) < 360 AND DATE >= '2017-08-31' AND DATE <= '2017-09-30' AND HOUR >= 9 AND HOUR < 22, 1 , 0) AS ex_z1z2z3z4z5_num_3to6_prev,
SUM(IF(goinside = 1 AND (zone1+zone2+zone3+cashiertime+special) >= 360 AND (zone1+zone2+zone3+cashiertime+special) < 600 AND DATE >= '2017-10-01' AND DATE <= '2017-10-31' AND HOUR >= 9 AND HOUR < 22, zone1+zone2+zone3+cashiertime+special , 0) AS ex_z1z2z3z4z5_num_6to10,
SUM(IF(goinside = 1 AND (zone1+zone2+zone3+cashiertime+special) >= 360 AND (zone1+zone2+zone3+cashiertime+special) < 600 AND DATE >= '2017-08-31' AND DATE <= '2017-09-30' AND HOUR >= 9 AND HOUR < 22, 1 , 0) AS ex_z1z2z3z4z5_num_6to10_prev,
SUM(IF(goinside = 1 AND (zone1+zone2+zone3+cashiertime+special) < 180 AND DATE >= '2017-10-01' AND DATE <= '2017-10-31' AND HOUR >= 9 AND HOUR < 22, zone1+zone2+zone3+cashiertime+special , 0) AS ex_z1z2z3z4z5_num_less3,
SUM(IF(goinside = 1 AND (zone1+zone2+zone3+cashiertime+special) < 180 AND DATE >= '2017-08-31' AND DATE <= '2017-09-30' AND HOUR >= 9 AND HOUR < 22, 1 , 0) AS ex_z1z2z3z4z5_num_less3_prev,
SUM(IF(goinside = 1 AND (zone1+zone2+zone3+cashiertime+special) >= 60 AND (zone1+zone2+zone3+cashiertime+special) < 180 AND DATE >= '2017-10-01' AND DATE <= '2017-10-31' AND HOUR >= 9 AND HOUR < 22, zone1+zone2+zone3+cashiertime+special , 0) AS ex_z1z2z3z4z5_num_1to3,
SUM(IF(goinside = 1 AND (zone1+zone2+zone3+cashiertime+special) >= 60 AND (zone1+zone2+zone3+cashiertime+special) < 180 AND DATE >= '2017-08-31' AND DATE <= '2017-09-30' AND HOUR >= 9 AND HOUR < 22, 1 , 0) AS ex_z1z2z3z4z5_num_1to3_prev,
SUM(IF(goinside = 1 AND (zone1+zone2+zone3+cashiertime+special) >= 600 AND (zone1+zone2+zone3+cashiertime+special) < 900 AND DATE >= '2017-10-01' AND DATE <= '2017-10-31' AND HOUR >= 9 AND HOUR < 22, zone1+zone2+zone3+cashiertime+special , 0) AS ex_z1z2z3z4z5_num_10to15,
SUM(IF(goinside = 1 AND (zone1+zone2+zone3+cashiertime+special) >= 600 AND (zone1+zone2+zone3+cashiertime+special) < 900 AND DATE >= '2017-08-31' AND DATE <= '2017-09-30' AND HOUR >= 9 AND HOUR < 22, 1 , 0) AS ex_z1z2z3z4z5_num_10to15_prev,
SUM(IF(goinside = 1 AND (zone1+zone2+zone3+cashiertime+special) < 60 AND DATE >= '2017-10-01' AND DATE <= '2017-10-31' AND HOUR >= 9 AND HOUR < 22, zone1+zone2+zone3+cashiertime+special , 0) AS ex_z1z2z3z4z5_num_less1,
SUM(IF(goinside = 1 AND (zone1+zone2+zone3+cashiertime+special) < 60 AND DATE >= '2017-08-31' AND DATE <= '2017-09-30' AND HOUR >= 9 AND HOUR < 22, 1 , 0) AS ex_z1z2z3z4z5_num_less1_prev,
SUM(IF(goinside = 1 AND (zone1+zone2+zone3+cashiertime+special) >= 900 AND DATE >= '2017-10-01' AND DATE <= '2017-10-31' AND HOUR >= 9 AND HOUR < 22, zone1+zone2+zone3+cashiertime+special , 0) AS ex_z1z2z3z4z5_num_over15,
SUM(IF(goinside = 1 AND (zone1+zone2+zone3+cashiertime+special) >= 900 AND DATE >= '2017-08-31' AND DATE <= '2017-09-30' AND HOUR >= 9 AND HOUR < 22, 1 , 0) AS ex_z1z2z3z4z5_num_over15_prev,
SUM(IF(goinside = 1 AND (zone1+zone2+zone3+cashiertime+special) >= 600 AND DATE >= '2017-10-01' AND DATE <= '2017-10-31' AND HOUR >= 9 AND HOUR < 22, zone1+zone2+zone3+cashiertime+special , 0) AS ex_z1z2z3z4z5_num_over10,
SUM(IF(goinside = 1 AND (zone1+zone2+zone3+cashiertime+special) >= 600 AND DATE >= '2017-08-31' AND DATE <= '2017-09-30' AND HOUR >= 9 AND HOUR < 22, 1 , 0) AS ex_z1z2z3z4z5_num_over10_prev,
SUM(IF(goinside = 1 AND TYPE = 1 AND DATE >= '2017-10-01' AND DATE <= '2017-10-31' AND HOUR >= 9 AND HOUR < 22, 1, 0) AS man,
SUM(IF(goinside = 1 AND TYPE = 2 AND DATE >= '2017-10-01' AND DATE <= '2017-10-31' AND HOUR >= 9 AND HOUR < 22, 1, 0) AS woman,
SUM(IF(goinside = 1 AND TYPE = 3 AND DATE >= '2017-10-01' AND DATE <= '2017-10-31' AND HOUR >= 9 AND HOUR < 22, 1, 0) AS couple,
SUM(IF(goinside = 1 AND TYPE = 4 AND DATE >= '2017-10-01' AND DATE <= '2017-10-31' AND HOUR >= 9 AND HOUR < 22, 1, 0) AS boy,
SUM(IF(goinside = 1 AND TYPE = 5 AND DATE >= '2017-10-01' AND DATE <= '2017-10-31' AND HOUR >= 9 AND HOUR < 22, 1, 0) AS girl,
SUM(IF(goinside = 1 AND TYPE = 6 AND DATE >= '2017-10-01' AND DATE <= '2017-10-31' AND HOUR >= 9 AND HOUR < 22, 1, 0) AS young_couple,
SUM(IF(goinside = 1 AND TYPE = 7 AND DATE >= '2017-10-01' AND DATE <= '2017-10-31' AND HOUR >= 9 AND HOUR < 22, 1, 0) AS old_man,
SUM(IF(goinside = 1 AND TYPE = 8 AND DATE >= '2017-10-01' AND DATE <= '2017-10-31' AND HOUR >= 9 AND HOUR < 22, 1, 0) AS old_woman,
SUM(IF(goinside = 1 AND TYPE = 9 AND DATE >= '2017-10-01' AND DATE <= '2017-10-31' AND HOUR >= 9 AND HOUR < 22, 1, 0) AS old_couple,
SUM(IF(goinside = 1 AND TYPE = 10 AND DATE >= '2017-10-01' AND DATE <= '2017-10-31' AND HOUR >= 9 AND HOUR < 22, 1, 0) AS family,
SUM(IF(goinside = 1 AND TYPE = 10 AND DATE >= '2017-10-01' AND DATE <= '2017-10-31' AND HOUR >= 9 AND HOUR < 22, family_total ELSE 0 END) AS family_total,
SUM(IF(goinside = 1 AND TYPE = 11 AND DATE >= '2017-10-01' AND DATE <= '2017-10-31' AND HOUR >= 9 AND HOUR < 22, 1, 0) AS GROUP,
SUM(IF(goinside = 1 AND TYPE = 11 AND DATE >= '2017-10-01' AND DATE <= '2017-10-31' AND HOUR >= 9 AND HOUR < 22, group_man ELSE 0 END) AS group_man,
SUM(IF(goinside = 1 AND TYPE = 11 AND DATE >= '2017-10-01' AND DATE <= '2017-10-31' AND HOUR >= 9 AND HOUR < 22, group_woman ELSE 0 END) AS group_woman,
SUM(IF(goinside = 1 AND TYPE = 12 AND DATE >= '2017-10-01' AND DATE <= '2017-10-31' AND HOUR >= 9 AND HOUR < 22, 1, 0) AS empty,
SUM(IF(goinside = 1 AND DATE >= '2017-10-01' AND DATE <= '2017-10-31' AND HOUR >= 9 AND HOUR < 22, 1, 0) AS total_entries,
SUM(IF(goinside = 1 AND TYPE = 1 AND DATE >= '2017-08-31' AND DATE <= '2017-09-30' AND HOUR >= 9 AND HOUR < 22, 1, 0) AS man_prev,
SUM(IF(goinside = 1 AND TYPE = 2 AND DATE >= '2017-08-31' AND DATE <= '2017-09-30' AND HOUR >= 9 AND HOUR < 22, 1, 0) AS woman_prev,
SUM(IF(goinside = 1 AND TYPE = 3 AND DATE >= '2017-08-31' AND DATE <= '2017-09-30' AND HOUR >= 9 AND HOUR < 22, 1, 0) AS couple_prev,
SUM(IF(goinside = 1 AND TYPE = 4 AND DATE >= '2017-08-31' AND DATE <= '2017-09-30' AND HOUR >= 9 AND HOUR < 22, 1, 0) AS boy_prev,
SUM(IF(goinside = 1 AND TYPE = 5 AND DATE >= '2017-08-31' AND DATE <= '2017-09-30' AND HOUR >= 9 AND HOUR < 22, 1, 0) AS girl_prev,
SUM(IF(goinside = 1 AND TYPE = 6 AND DATE >= '2017-08-31' AND DATE <= '2017-09-30' AND HOUR >= 9 AND HOUR < 22, 1, 0) AS young_couple_prev,
SUM(IF(goinside = 1 AND TYPE = 7 AND DATE >= '2017-08-31' AND DATE <= '2017-09-30' AND HOUR >= 9 AND HOUR < 22, 1, 0) AS old_man_prev,
SUM(IF(goinside = 1 AND TYPE = 8 AND DATE >= '2017-08-31' AND DATE <= '2017-09-30' AND HOUR >= 9 AND HOUR < 22, 1, 0) AS old_woman_prev,
SUM(IF(goinside = 1 AND TYPE = 9 AND DATE >= '2017-08-31' AND DATE <= '2017-09-30' AND HOUR >= 9 AND HOUR < 22, 1, 0) AS old_couple_prev,
SUM(IF(goinside = 1 AND TYPE = 10 AND DATE >= '2017-08-31' AND DATE <= '2017-09-30' AND HOUR >= 9 AND HOUR < 22, 1, 0) AS family_prev,
SUM(IF(goinside = 1 AND TYPE = 10 AND DATE >= '2017-08-31' AND DATE <= '2017-09-30' AND HOUR >= 9 AND HOUR < 22, family_total , 0) AS family_total_prev,
SUM(IF(goinside = 1 AND TYPE = 11 AND DATE >= '2017-08-31' AND DATE <= '2017-09-30' AND HOUR >= 9 AND HOUR < 22, 1, 0) AS grou_prev,
SUM(IF(goinside = 1 AND TYPE = 11 AND DATE >= '2017-08-31' AND DATE <= '2017-09-30' AND HOUR >= 9 AND HOUR < 22, group_man , 0) AS group_man_prev,
SUM(IF(goinside = 1 AND TYPE = 11 AND DATE >= '2017-08-31' AND DATE <= '2017-09-30' AND HOUR >= 9 AND HOUR < 22, group_woman , 0) AS group_woman_prev,
SUM(IF(goinside = 1 AND TYPE = 12 AND DATE >= '2017-08-31' AND DATE <= '2017-09-30' AND HOUR >= 9 AND HOUR < 22, 1, 0) AS empty_prev,
SUM(IF(goinside = 1 AND DATE >= '2017-08-31' AND DATE <= '2017-09-30' AND HOUR >= 9 AND HOUR < 22, 1, 0) AS total_entries_prev 
FROM data_1034_detail WHERE id_station IN (2399,2397) AND ((DATE >= '2017-10-01' AND DATE <= '2017-10-31' AND HOUR >= 9 AND HOUR < 22) OR (DATE >= '2017-08-31' AND DATE <= '2017-09-30' AND HOUR >= 9 AND HOUR < 22)) GROUP BY id_station, area_type

My problem is I cannot use index in select condition, Im using many condition field to it made the query speed was very very slow. Are there any way to improve this select query?

Rent Charter Buses Company
READ ALSO
Error while performing Update query- Error Code: 1292. Incorrect datetime value: &#39;&#39;YYYYMMDDHHMMSSZ"

Error while performing Update query- Error Code: 1292. Incorrect datetime value: ''YYYYMMDDHHMMSSZ"

I am trying to update a datetime which is in String format YYYYMMDDHHMMSSZ into YYYY-MM-DD HH:MM:SS

260
MYSQL : How to insert with multiply from another table?

MYSQL : How to insert with multiply from another table?

I have two tables, the first one is barang_laundry and second is laundry** barang_laundry :

172