KnowledgeHub
Questions
Tags
Users
Search
Alex Rivera
|
Logout
Edit Question
Title
Body
I have some problem using this query, it appears that i cannot use alias as reference on group by query SELECT v.V_VEHICLEID, v.V_LICENSENO, ROUND((MAX(IF(DAY(WP_DATETIME) = 1, WP_ODOMETER, NULL)) - MIN(IF(DAY(WP_DATETIME) = 1, WP_ODOMETER, NULL))) / 1000) AS TRIP1, ..., ROUND((MAX(IF(DAY(WP_DATETIME) = 31, WP_ODOMETER, NULL)) - MIN(IF(DAY(WP_DATETIME) = 31, WP_ODOMETER, NULL))) / 1000) AS TRIP31, ROUND(MAX(IF(DAY(WP_DATETIME) = 1, WP_FUELREAD, NULL)) - MIN(IF(DAY(WP_DATETIME) = 1, WP_FUELREAD, NULL)),2) AS FUEL1, ..., ROUND(MAX(IF(DAY(WP_DATETIME) = 31, WP_FUELREAD, NULL)) - MIN(IF(DAY(WP_DATETIME) = 31, WP_FUELREAD, NULL)),2) AS FUEL31, (SELECT TRIP1 / FUEL1) AS ECON1, ..., (SELECT TRIP31 / FUEL31) AS ECON31 FROM VEHICLES v JOIN WAYPOINTS wp on wp.WP_VEHICLEID = v.V_VEHICLEID WHERE MONTH(wp.WP_DATETIME) = '6' AND v.V_USER = 'tc' GROUP BY wp.WP_VEHICLEID the error Reference 'TRIP1' not supported (reference to group function) any solution?
Tags (comma-separated)
Save Edits
Cancel