selecting average response times from two database tables

Question

I have two tables in MySQL - tickets and ticket_updates

With the following structure:

tickets

sequence
ticketnumber
datetime

ticket_updates

sequence
ticketnumber
datetime

the relationship is: tickets.ticketnumber = ticket_updates.ticketnumber

i want to find out the Average response time for a certain day based on when rows are inserted into ticket_updates

I tried this Query:

SELECT avg(response_seconds) as s FROM 
    (SELECT time_to_sec(timediff(min(u.datetime), u.datetime)) AS response_seconds 
    FROM tickets t JOIN ticket_updates u ON t.ticketnumber = u.ticketnumber 
    WHERE u.type = 'update' and t.customer = 'Y' and DATE(u.datetime) = '2016-04-18' 
    GROUP BY t.ticketnumber) 
    AS r

but everytime, its returning 0.000

Tudor Constantin · Accepted Answer · 2016-04-18 13:48:10Z

1

I think the problem is with:

(SELECT time_to_sec(timediff(min(u.datetime), u.datetime)) AS response_seconds

which returns 0 on my console. Try with:

( select time_to_sec( timediff( from_unixtime( floor( UNIX_TIMESTAMP(u.datetime)/60 )*60 ), u.datetime) ) ) as response_seconds;

like

SELECT avg(response_seconds) as s FROM 
    ( select time_to_sec( timediff( from_unixtime( floor( UNIX_TIMESTAMP(u.datetime)/60 )*60 ), u.datetime) ) ) as response_seconds 
    FROM tickets t JOIN ticket_updates u ON t.ticketnumber = u.ticketnumber 
    WHERE u.type = 'update' and t.customer = 'Y' and DATE(u.datetime) = '2016-04-18' 
    GROUP BY t.ticketnumber) 
    AS r

edited Apr 18, 2016 at 13:48

answered Apr 18, 2016 at 13:18

Tudor Constantin

26.9k7 gold badges54 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

charlie Over a year ago

thats now just returning NULL

Tudor Constantin Over a year ago

Yeah, on my machine also returns NULL, I totally rewrote my answer, with a different solution

charlie Over a year ago

thats returning a syntax error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM tickets t JOIN ticket_updates u ON t.ticketnumber = u.ticketnumber WHE' at line 3

Collectives™ on Stack Overflow

selecting average response times from two database tables

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related