CODEWITHSUNDEEP
mysqlselect
RaGe10940
RaGe10940

Reputation: 667

MySQL AVG TimeDiff of Multiple rows

I have a query that I need for it to select the average time difference of two different times from two separate tables.

That seemed easy until the next part of the query came in : I have to group by the reason for a student coming into the office. So now this query needs to :

  1. Select the reason (why)
  2. Count how many times a student has come in for that reason count(why)
  3. Then I need to check the AVG time from the starttime to finishtime. Keep in mind that I am grouping by why. This means that I need the timediff to compute difference for all records that fall within the reason for the students visit.

I wrote this query : 

 SELECT why, 
    count(why), 
    SEC_TO_TIME(AVG(MAX(support.finishtime) - session.signintime)) 
    FROM session 
    LEFT JOIN support  
        ON support.session_id = session.session_id  
    WHERE status = 3  
    GROUP BY why;

However I get a error :

ERROR 1111 (HY000): Invalid use of group function

I don't seem to understand this problem. From reading past questions they are considering to use having? But I don't understand how or even where in this situation where to add the having clause.

Any help would be much appreciated.

Thank you

Upvotes: 1

Views: 2313

Answers (2)

RaGe10940
RaGe10940

Reputation: 667

SELECT 
session.why AS Reason, 
COUNT(session.why) AS Count, 
SEC_TO_TIME(AVG(TIMEDIFF(t.fin, session.signintime))) AS Time
FROM session 
LEFT JOIN (SELECT support.session_id, MAX(support.finishtime) AS fin 
FROM support 
GROUP BY support.session_id) AS t 
    ON session.session_id = t.session_id 
WHERE session.status = 3  
GROUP BY session.why 
ORDER BY session.signintime DESC

This does the job perfectly! I just got it a couple hours ago, I never really worked with sub queries so my professor helped me out with it.

Upvotes: 1

Brian Hoover
Brian Hoover

Reputation: 7991

Ok, looking over this a little bit, I'm thinking that what you want is a nested query to get the max support record.

One way to do this is:

 SELECT why, 
    count(why), 
    SEC_TO_TIME(AVG((select max(finishtime) from 
              support where support.session_id = session.session_id) -
        session.signintime))
    FROM session 
    WHERE status = 3  
    GROUP BY why;

I've create an SQL Fiddle, so you can look at the table structure that I was using.

Upvotes: 0

Related Questions