Mysql – thesql: strange error 1111 invalid use of group by


Following is a query i am trying to run.

select location_data.trip_code,sum(max(device_time)-min(device_time)) from location_data,trip_management 
where location_data.source_id=3 and location_data.trip_code=trip_management.trip_code
group by location_data.trip_code

there are various trips identified by trip_code in both trip_managemnet and location_data tables.these trips are taken by a single uniquely identified user (source_id=)3. what i am trying to do here is to sum all the time differences for each trip and then convert it into hh:mm:ss using the sec_to_time function to display the total time it took user 3 to take all of his trips.

the problem with above query is that it generates error 1111 as soon as i apply sum() over the difference of max and min device_time of each trip. i cant afford a subquery because this in itself is a subquery in a larger query.

I hope i explained the problem well.

Best Solution

The issue here is that you are attempting to apply an aggregate SUM() over the aggregates MAX(),MIN(), but in fact the two levels operate on different groups. The inner one groups over location_data.trip_code, and the outer one groups over the result of that. You'll need to wrap it in a subquery:

  /* Outer query sums the inner aggregates */
  SUM(max_time - min_time) AS time_sum
    /* Inner aggregates return the max & min times only */
    max(device_time) AS max_time,
    min(device_time) AS min_time
     INNER JOIN trip_management ON location_data.trip_code = trip_management.trip_code
  GROUP BY location_data.trip_code
) first_group
GROUP BY trip_code

I've also replaced you implicit join with an explicit INNER JOIN, which is the preferred syntax nowadays.