Search code examples
mysqlrelational-databaseinner-join

MySQL: How to perfom a multiple-table query including a calculated sum?

Firstly, the relationships between the tables in question:enter image description here

I'm attempting to write a query which retrieves: people_has_trips.people_personID, people_has_trips_tripID, seminarname, agent, and "Amount," which is intended to be the total of all entries in the Payments table for a given entry in the Trip table. This is relevant because this query is meant to be used in a subform on the Access frontend I'm building. In simple terms, the concept for this query is: Which trip(s) has this person been on, and how much money was spent, total, on each trip?

Here's my code so far:

SELECT 
    people_has_trips.people_PersonID,
    people_has_trips.trips_tripID,
    people_has_trips.seminar_seminarID,
    seminar.seminarname,
    agentref.agent,
    payments.amount
FROM
    (seminar
    INNER JOIN (((trips
    INNER JOIN people_has_trips ON trips.tripID = people_has_trips.Trips_tripID)
    INNER JOIN payments ON trips.tripID = payments.trips_tripID)
    INNER JOIN agentnotes ON trips.tripID = agentnotes.Trips_tripID) ON seminar.seminarid = people_has_trips.seminar_seminarid)
        INNER JOIN
    agentref ON trips.agentref_agentid = agentref.agentid;

Which returns the following:
Query Result

So we're most of the way there, obviously the final version will be cleaned up a bit (seminarID is unneccesary, for example), so my only question is: what do I need to do so that instead of "amount" showing every individual payment, it's a total of all payments for a trip?

Bonus: The keen-eyed will notice that "seminar" is actually a optional foreign key, and therefore this query, being made of inner joins, excludes any combination of people and trips which does not include a seminar. This is not intended, and another problem I'll need to solve.

Solution

  • Sorry for rearranging the SQL, but I had a hard time following what was going on until I reorganised it.

    If you GROUP BY the columns other than the payment value you can use aggregate functions like SUM to get the total of the payments.

    SELECT 
    people_has_trips.people_PersonID,
    people_has_trips.trips_tripID,
    people_has_trips.seminar_seminarID,
    seminar.seminarname,
    agentref.agent,
    SUM(payments.amount)
FROM seminar
INNER JOIN people_has_trips
    ON
    people_has_trips.seminar_seminarid = seminar.seminarid
INNER JOIN trips
    ON
    people_has_trips.Trips_tripID = trips.tripID
INNER JOIN payments
    ON
    trips.tripID = payments.trips_tripID
INNER JOIN agentnotes
    ON
    agentnotes.Trips_tripID = trips.tripID
INNER JOIN agentref
    ON
    agentref.agentid = trips.agentref_agentid
GROUP BY
    people_has_trips.trips_tripID,
    people_has_trips.people_PersonID,
    people_has_trips.seminar_seminarID,
    seminar.seminarname,
    agentref.agent
;