Databases
StackOverflow2013
Postsdbo
POHow to get the sum of a column from combined tables in mySQL?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to get the sum of a column from combined tables in mySQL?
    text
    copied!<p>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!</p> <p>I have two tables in a mySQL-database, <em>event</em> and <em>route</em>:</p> <p><strong>event:</strong></p> <blockquote> <p>id | date | destination | drivers | passengers | description | executed</p> </blockquote> <p><strong>route:</strong></p> <blockquote> <p>name | distance</p> </blockquote> <ul> <li><em>drivers</em> contains a string with the usernames of the registered drivers in an event on the form "jack:jill:john".</li> <li><em>destination</em> contains the event destination (oh, really?) and its value is always the same as one of the values in the field <em>name</em> in the table <em>route</em> (i.e. the destination must already exist in <em>route</em>). </li> <li><em>executed</em> tells if the event is upcoming (0) or already executed (1).</li> <li><em>distance</em> is the distance to the destination in km from the home location.</li> </ul> <p>What I want is to get the total distance covered for one specific user, only counting already executed events.</p> <p>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.</p> <p>I know I can use something like <code>...WHERE drivers LIKE '%jill%' AND executed = 1</code> to get the executed events where Jill was driving, and <code>SUM()</code> to get the total distance, but how do I combine the two tables and get it all to work?</p> <p>Your help is very much appreciated!</p> <p>/Linus</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload