CODEWITHSUNDEEP
mysql
Gloytos htyqo
Gloytos htyqo

Reputation: 355

mysql: count sum of column created via sql

i show first and last log for each day from user 1 ,

alo i calculate the time differnece between first and last log.

so what i want is to calculate total sum of time difference something like

SUM(total)

here is what i run

SELECT id,device,userid,time, MIN(time) as Min, MAX(time) as Max, TIMEDIFF(MAX(time), MIN(time)) as total
FROM records WHERE userid='1' GROUP BY DATE(time)

result IMAGE

so i want to get like
totalsum = =25:31:47

Upvotes: 1

Views: 126

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

  • You can use your current query results in a Derived table and calculate the sum.
  • Also, you will need to first convert the time to seconds using TIME_TO_SEC() function, and after doing the SUM, you will need to reconvert it back to time by using SEC_TO_TIME() function.

Try the following: 

SELECT SEC_TO_TIME( SUM( TIME_TO_SEC(derived_t.total) ) ) AS total_time_diff
FROM 
(
 SELECT TIMEDIFF(MAX(time), MIN(time)) as total
 FROM records 
 WHERE userid='1' 
 GROUP BY DATE(time) 
) AS derived_t

Upvotes: 1

Related Questions