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.
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:
SELECT
trip_code,
/* Outer query sums the inner aggregates */
SUM(max_time - min_time) AS time_sum
FROM (
SELECT
location_data.trip_code,
/* Inner aggregates return the max & min times only */
max(device_time) AS max_time,
min(device_time) AS min_time
FROM
location_data,
INNER JOIN trip_management ON location_data.trip_code = trip_management.trip_code
WHERE
location_data.source_id=3
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.