Grempels Gabriel Grempels Gabriel - 24 days ago 6
MySQL Question

Sum mysql results for each pair

Table locations :

+----+-----------+-----------+---------------------+
| Id | Old Value | New Value | Date |
+----+-----------+-----------+---------------------+
| 1 | Home | Work | 2016-11-01 09:00:00 |
| 2 | Work | Home | 2016-11-01 09:25:00 |
| 3 | Home | Work | 2016-11-01 09:44:59 |
| 4 | Work | Home | 2016-11-01 10:11:50 |
| 5 | Home | Work | 2016-11-01 10:25:30 |
| 6 | Work | Home | 2016-11-01 11:01:15 |
+----+-----------+-----------+---------------------+


My Query :

SELECT loc1.*,loc2.date AS loc2_date, SUM(CAST(TIMESTAMPDIFF(MICROSECOND,loc1.date,loc2.date)/1000000 AS DECIMAL(20))) AS time_diff
FROM locations AS loc1
LEFT JOIN locations AS loc2 ON loc1.old_value=loc2.new_value AND loc2.id > loc1.id
WHERE old_value="Home"


My Query Result :

Id: 1
Old Value: Home
New Value: Work
Date: 2016-11-01 09:00:00
Loc2_date: 2016-11-01 09:25:00
Time_fidd: 1500

Id: 1
Old Value: Home
New Value: Work
Date: 2016-11-01 09:00:00
Loc2_date: 2016-11-01 10:11:50
Time_diff: 4310

Id: 1
Old Value: Home
New Value: Work
Date: 2016-11-01 09:00:00
Loc2_date: 2016-11-01 11:01:15
Time_diff: 7275

Id: 3
Old Value: Home
New Value: Work
Date: 2016-11-01 09:44:59
Loc2_date: 2016-11-01 10:11:50
Time_diff: 1611

Id: 3
Old Value: Home
New Value: Work
Date: 2016-11-01 09:44:59
Loc2_date: 2016-11-01 11:01:15
Time_diff: 4576

Id: 5
Old Value: Home
New Value: Work
Date: 2016-11-01 10:25:30
Loc2_date: 2016-11-01 11:01:15
Time_diff: 2145


I wanna sum the date to see all the seconds I was at work. If I group by Id will show exactly what I need but when I sum the difference between
loc1.date
and
loc2.date
the query will skip group by and keep returning all the rows. Please help.

What I need from my query :

Id: 1
Old Value: Home
New Value: Work
Date: 2016-11-01 09:00:00
Loc2_date: 2016-11-01 09:25:00
Time_fidd: 1500

Id: 3
Old Value: Home
New Value: Work
Date: 2016-11-01 09:44:59
Loc2_date: 2016-11-01 10:11:50
Time_diff: 1611

Id: 5
Old Value: Home
New Value: Work
Date: 2016-11-01 10:25:30
Loc2_date: 2016-11-01 11:01:15
Time_diff: 2145

A J A J
Answer

Updated

Try this.

SELECT tablea.id, tablea.`Old_value`, tablea.`New_value`, tablea.`Date`, tablea.`Date` Loc2_date, SUM(tablea.time_diff) AS time_diff 
FROM 
(
    SELECT loc1.`id`, loc1.`Old_value`, loc1.`New_value`, loc1.`Date`, loc2.`Date` Loc2_date, CAST(ABS(TIMESTAMPDIFF(MICROSECOND, loc2.date, loc1.date)/1000000) AS DECIMAL(20)) time_diff
    FROM locations loc1 INNER JOIN locations loc2
    ON loc1.`Old_value`=loc2.`New_value` AND loc2.`id`>loc1.`id`
    WHERE loc1.`Old_value`="Home"
    GROUP BY loc1.`id`
) tablea    

Example:

SELECT tableb.*
FROM
(
    SELECT tablea.id, tablea.`Old_value`, tablea.`New_value`, tablea.`Date`, tablea.`Date` Loc2_date, SUM(tablea.time_diff) AS time_diff 
    FROM 
    (
        SELECT loc1.`id`, loc1.`Old_value`, loc1.`New_value`, loc1.`Date`, loc2.`Date` Loc2_date, CAST(ABS(TIMESTAMPDIFF(MICROSECOND, loc2.date, loc1.date)/1000000) AS DECIMAL(20)) time_diff
        FROM locations loc1 INNER JOIN locations loc2
        ON loc1.`Old_value`=loc2.`New_value` AND loc2.`id`>loc1.`id`
        WHERE loc1.`Old_value`="Home"
        GROUP BY loc1.`id`
    ) tablea    
) tableb

Old Answer.

You can try group_by for this. Add GROUP BY loc1.id after WHERE clause.

SELECT loc1.*,loc2.date AS loc2_date, SUM(CAST(TIMESTAMPDIFF(MICROSECOND,loc1.date,loc2.date)/1000000 AS DECIMAL(20)))
FROM locations AS loc1
LEFT JOIN locations AS loc2 ON loc1.old_value=loc2.new_value AND loc2.id > loc1.id
WHERE old_value="Home"
GROUP BY loc1.id