Sum of rows with join

This is the current table layout.

• There are 3 legs
• 开发者_JS百科Each leg has 2 points, where is_start = 1 is the start of the leg, and is_start is the end of the leg.
• When the user check in at a point, a entry in points_user are created.

In this application you have multiple legs which has 2 points where one marks the start of the leg, where the other marks the end of the leg. So the sum of User's (with id = 2) Leg (with id= 1) is points_users.created where points_users.leg_id = 1 and points_users.user_id = 2 and points_users.is_start = 0 minus points_users where is_start = 1 (and the other parameters stay the same). And that's for just one leg.

What I would like is to sum all the time differences for each leg, we get the data like this:

| User.id | User.name | total_time |
| 1       | John      | 129934     |

Anyone know how I can join these tables and sum it up grouped by user?

(No, this is not homework)

As far as I got:

SELECT
( `end_time` - `start_time` ) AS `diff`
FROM
(
    SELECT SUM(UNIX_TIMESTAMP(`p1`.`created`)) AS `start_time`
    FROM `points_users` AS `pu1`
    LEFT JOIN `points` AS `p1` ON `pu1`.`point_id` = `p1`.`id`
    WHERE `p1`.`is_start` = 1
) AS `start_time`,
(
    SELECT SUM(UNIX_TIMESTAMP(`pu2`.`created`)) AS `end_time`
    FROM `points_users` AS `pu2`
    LEFT JOIN `points` AS `p2` ON `pu2`.`point_id` = `p2`.`id`
    WHERE `p2`.`is_start` = 0
) AS `end_time`

Try this:

select users.user_id,
    users.user_name,
    SUM(timeDuration) totalTime
from users
join (
    select 
        pStart.User_id,
        pStart.leg_id,
        (pEnd.created - pStart.created) timeDuration                
    from (select pu.user_id,  pu.leg_id,  pu.created
        from points_users pu
        join points p on  pu.id = p.point_id and pu.leg_id = p.leg_id
        where p.is_start = 1 ) pStart
    join  (select pu.user_id, pu.leg_id,  pu.created
        from points_users pu
        join points p on  pu.id = p.point_id and pu.leg_id = p.leg_id
        where p.is_start = 0 ) pEnd
    on      pStart.user_id = pEnd.user_id
    and     pStart.leg_id = pEnd.leg_id
    ) tt
on users.user_id = tt.user_id
group by users.user_id, users.user_name

Subquery gets the time duration for each user/leg, and main query then sums them for all the legs of each user.

EDIT: Added the points table now that I can see your attempt at a query.

The simplest way is to join points_users to itself:

select leg_start.user_id, sum(leg_end.created - leg_start.created)
from points_users leg_start
join points_users leg_end on leg_start.user_id = leg_end.user_id
    and leg_start.leg_id = leg_end.leg_id
join points point_start on leg_start.point_id = point_start.id
join points point_end on leg_end.point_id = point_end.id
where point_start.is_start = 1 and point_end.is_start = 0
group by leg_start.user_id

Some people prefer to put those is_start filters in the join condition, but since it's an inner join that's mainly just a point of style. If it were an outer join, then moving them from the WHERE to the JOIN could have an effect on the results.