CODEWITHSUNDEEP
mysqlsql
Steve
Steve

Reputation: 2073

Sum Team Competition Values with SQL

Overview

I have a bunch of data on a competition I'm holding and I want to present it in a better format.

There's 4 tables; the first two are self-explanatory, the points and extras table are essentially the exact same thing, they're just stored in different tables with slightly different column names.

Data

    users
    +----+---------------+------+
    | id |     name      | team |
    +----+---------------+------+
    |  1 | John Doe      |    1 |
    |  2 | Jane Lane     |    1 |
    |  3 | Jack Black    |    4 |
    |  4 | Dan Bam       |    3 |
    |  5 | Pam Jan       |    2 |
    |  6 | Pop Tart      |    2 |
    |  7 | John Q        |    1 |
    |  8 | Hugo Strange  |    3 |
    |  9 | Jimmy Neutron |    2 |
    +----+---------------+------+

    teams
    +----+-----------------+
    | id |      name       |
    +----+-----------------+
    |  1 | Team Fun        |
    |  2 | The Dream Team  |
    |  3 | In It To Win It |
    |  4 | Buddies         |
    +----+-----------------+

    points
    +---------+--------+------------+
    | user_id | points |   event    |
    +---------+--------+------------+
    |       1 | 2      | Basketball |
    |       2 | 4      | Basketball |
    |       5 | 1      | Basketball |
    |       8 | 3      | Basketball |
    |       9 | 5      | Basketball |
    |       2 | 8      | Volleyball |
    |       5 | 5.5    | Volleyball |
    |       6 | 6.5    | Volleyball |
    |       7 | 2      | Volleyball |
    |       8 | 4      | Volleyball |
    |       9 | 9.5    | Volleyball |
    |       1 | 2.5    | Dodgeball  |
    |       3 | 3      | Dodgeball  |
    |       4 | 4      | Dodgeball  |
    |       6 | 9      | Dodgeball  |
    |       7 | 2.5    | Dodgeball  |
    |       9 | 3      | Dodgeball  |
    +---------+--------+------------+

    extras
    +---------+--------+---------------------+
    | user_id | points |     description     |
    +---------+--------+---------------------+
    |       1 | 5      | Great Sportsmanship |
    |       3 | 10     | Team Player         |
    |       8 | 5.5    | Most Improved       |
    +---------+--------+---------------------+

What I'm Trying To Do

I want to write a query to return all the events (and "extras") a specific team participated in, the total points from all members of the team, and the participating members in that event.

Example below uses Team Fun (Team 1):

+---------------------+--------+--------------------+------------+
|        event        | points |      members       | members_id |
+---------------------+--------+--------------------+------------+
| Basketball          |      6 | John Doe,Jane Lane | 1,2        |
| Volleyball          |     10 | Jane Lane,John Q   | 2,7        |
| Dodgeball           |      5 | John Doe,John Q    | 1,7        |
| Great Sportsmanship |      5 | John Doe           | 1          |
+---------------------+--------+--------------------+------------+

If anyone could help me with figuring this out, I'd appreciate it!

SQLFiddle

This is a SQLFiddle with the data schema above - http://sqlfiddle.com/#!2/e8f97a

Upvotes: 5

Views: 165

Answers (1)

lc.
lc.

Reputation: 116458

You can use a UNION to get the extras and points together:

SELECT user_id, points, event
FROM points
UNION ALL
SELECT user_id, points, description AS event
FROM extras

Then using this, you can compile your info with a SUM and a couple of GROUP_CONCATs:

SELECT P.event, SUM(P.points) AS points, 
    GROUP_CONCAT(U.name) AS members, GROUP_CONCAT(U.id) AS members_id
FROM teams T
INNER JOIN users U ON T.id = U.team
INNER JOIN
(
    SELECT user_id, points, event
    FROM points
    UNION ALL
    SELECT user_id, points, description AS event
    FROM extras
) P ON U.id = P.user_id
WHERE T.id = @teamId
GROUP BY P.event

SQL Fiddle Example

Upvotes: 1

Related Questions