Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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
     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.
                thanks alot :) but i was hoping to avoid a subquery to achieve the results. as i mentioned, this query initself is a subquery in a larger query. having nested subqueries will result in decreased performance, wont it? is there a way subquery could be avoided keeping the schema as it is?
– Salik
                May 1, 2013 at 14:48
                @user1451836 A FROM subquery can't really be avoided in this situation because the GROUP BY clauses need to be applied in different contexts.  The performance here won't be nearly as poor as if you did them as subselects inside the SUM(). That could be orders of magnitude slower.  Your other option would be to cache the subquery as a materialized view, if it doesn't change often.
– Michael Berkowski
                May 1, 2013 at 15:27
                views wont work because incoming data is real-time, and to create views it will add a bit more complexity to the system. so, i assume this is the answer for my question. thanks Michael :)
– Salik
                May 1, 2013 at 15:50
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.