I have the following query as below.
SELECT Day(vehicleTrip.startDateTime) As day, Month(vehicleTrip.startDateTime) As month, Sum(TIMESTAMPDIFF(SECOND, startDateTime, endDateTime )) As totalDuration, count(vehicleTrip.startDateTime) As totalTrip, sum(endMileage-startMileage) As totalMleage FROM vehicleTrip WHERE vehicleTrip.vehicleID=:vehicleID AND vehicleTrip.vehicleEndCodeID!=1 AND vehicleTrip.startDateTime BETWEEN :startDateTime And :endDateTime Group By Day(vehicleTrip.startDateTime),Month(vehicleTrip.startDateTime)
Currently it works fine cause it can group by the daily date the sum of
duration, totalTrip, totalMileage etc. But only issue I want the date to be arrange by desc. I have tried putting the order by startDateTime desc it gives
me error that its not in GROUP By clause and containts nonaggregated column. I have tried many method like even putting the
Max(vehicleTrip.startDateTime) still the same. How best can I resolve this ?
Below is my table design with its index.
CREATE TABLE `vehicleTrip` ( `vehicleEndCodeID` tinyint NOT NULL DEFAULT '1', `vehicleID` mediumint NOT NULL, `startDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `endDateTime` timestamp NULL DEFAULT NULL, `startMileage` float(11,3) DEFAULT NULL, `endMileage` float(11,3) DEFAULT NULL, `startLatitude` float(10,8) NOT NULL, `startLongitude` float(11,8) NOT NULL, `endLatitude` float(10,8) DEFAULT NULL, `endLongitude` float(11,8) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ALTER TABLE `vehicleTrip` ADD PRIMARY KEY (`vehicleID`,`startDateTime`) USING BTREE; COMMIT;