How to get the sum of a column from combined tables in mySQL?

Question

I've been trying to write a mySQL-statement for the scenario below, but I just can't get it to work as intended. I would be very grateful if you guys could help me get it right!

I have two tables in a mySQL-database, event and route:

event:

id | date | destination | drivers | passengers | description | executed

route:

name | distance

• drivers contains a string with the usernames of the registered drivers in an event on the form "jack:jill:john".
• destination contains the event destination (oh, really?) and its value is always the same as one of the values in the field name in the table route (i.e. the destination must already exist in route).
• executed tells if the event is upcoming (0) or already executed (1).
• distance is the distance to the destination in km from the home location.

What I want is to get the total distance covered for one specific user, only counting already executed events.

E.g., if Jill has been registered as a driver in two executed events where the distances to the destinations are 50km and 100km respectively, I would like the query to return the value 150.

I know I can use something like ...WHERE drivers LIKE '%jill%' AND executed = 1 to get the executed events where Jill was driving, and SUM() to get the total distance, but how do I combine the two tables and get it all to work?

Your help is very much appreciated!

/Linus

Answer 1

I haven't use MySQL for years, so sorry if I've got the syntax wrong, but something like this should do it:

In generic SQL:

select sum(distance) from route
 join event on route.name = event.destination 
 where drivers like '%jill%' AND executed = 1

Or not using JOIN:

select sum(distance) from route, event
 where drivers like '%jill%' AND executed = 1
 and route.name = event.destination

Answer 2

Stuart's answer shows you how to get the sum of the column, but I just want to note that:

...WHERE drivers LIKE '%jill%'...

will return any event with a driver whose name contains the letters 'jill'.

Secondly, this database design doesn't seem to be normalized. You have driver names and route names repeated. If you normalize the database and have something like:

participant

id | name | role

event

id | date | route_id | description | executed

route

id | name | distance

participant_event

id | participant_id | event_id

then it would be a lot easier to work with the data.

Then if you wanted to implement a user search, you could make the query:

SELECT id FROM participant WHERE
    name LIKE '%jill%' AND
    role='driver';

Then if the query returns more than one result, let the user/application choose the correct driver and then run a SELECT SUM like Stuart's query:

SELECT SUM(r.distance) FROM route r
    JOIN event e ON e.route_id=r.id
    JOIN participant_event pe ON e.id=pe.event_id
    JOIN participant p ON pe.participant_id=p.id
    WHERE p.id=?;

Otherwise, the only way to ensure that you're only getting the total distance driven by one driver is to do something like this (assuming drivers is comma-delimited):

...WHERE LCASE(drivers)='jill' OR
    drivers LIKE 'jill, %' OR
    drivers LIKE '%, jill' OR
    drivers LIKE '%, jill,%';