SQL: Sum values from two different tables

Question

Didn't want to do this (ask for help) but I'm stumped...

I'm writing a hockey pool program and one of the functions is to calculate a total amount of weekly points for all players (Goalies and Skaters) "playing" for a particular Pool Team.

I've got 3 tables.

    **PoolTeams** 
PoolTeamID      PoolTeamName
----------      ------------ 
1               Team A 
2               Team B 
3               Team C

**MondaySkaterStats**
Player      Weeknumber       PoolTeam        PTS       Status
------      ----------       --------        ---       ------
10          1                1               15        True
11          2                1               3         False
13          3                2               5         True
40          1                1               3         True
41          2                1               5         False
43          3                2               1         True

**MondayGoalieStats** 
Player           Weeknumber       PoolTeam        PTS       Status
------           ----------       --------        ---       ------ 
20               1                3               4         False 
21               2                1               3         True 
22               3                2               5         False
50               1                3               4         False 
51               2                1               3         True 
52               1                1               7         True

The actual database is much bigger, I have truncated is considerably for clarity.

Also, MondayGoalieStats table has a few extra columns which are not relevant to the outcome of the results.

So let's say I want to sum up points of all players playing for each Pool Team (Skaters and Goalies) that have a Status of TRUE and I'm interested in Week #1 only.

Here is an SQL Query I came up with. It works great when summing up MondaySkaterStats points but for whatever reason it doesn't seem to properly sum up points in the MondayGalieStats table. I decided to go with INNER JOIN since the Player and Goalie tables are not exactly the same. I used Weeknumber as the common point between the two.

SELECT PT.PoolTeamName, SUM(MS.PTS) AS PlayerSum, SUM(MG.PTS) as GoalieSum
FROM PoolTeams PT, MondaySkaterStats MS 
INNER JOIN MondayGoalieStats MG ON (MS.Weeknumber=MG.Weeknumber) 
WHERE MS.Weeknumber=1 
AND PT.PoolTeamID = MS.PoolTeam 
AND MS.PoolTeam = MG.PoolTeam 
AND MS.Status = True 
AND MG.Status = True
GROUP BY PT.PoolTeamName

Any help would be greatly appreciated.... thanks!

You've got a weird mix of syntaxs here...use inner join syntax (preferable) or the older join in the where clauses, not both. You even have mg joined in using both methods (poolteam in where clause, week number in on clause) — Twelfth
– Twelfth, Commented Mar 21, 2018 at 16:28
The database structure seems a bit complicated... wouldn't it be possible to have a global table "MondayStats" with an extra column storing "skaters" or "goalie" ? It would be much easier to request. — scandel
– scandel, Commented Mar 21, 2018 at 16:36
That query is hard to read and messed up. I am not even sure that is valid syntax. — paparazzo
– paparazzo, Commented Mar 21, 2018 at 16:40
paparazzo, yes... agreed. I'm just getting into the SQL thing and the JOINs confuse the heck out of me...as it is apparently evident. — navaidstech
– navaidstech, Commented Mar 22, 2018 at 15:01

willoller · Accepted Answer · 2018-03-21 17:12:32Z

2

You could use UNION to create an intermediate table, and add a label to each table to keep the positions separate. Then, you can group by the team and the position. This will result in 2 lines per Team, with 1 line per position. Finally, JOIN the PoolTeam table to get the PoolTeamName for display.

(different engines will have different syntax; I am used to MySQL)

SELECT PoolTeams.PoolTeamName, SUM(Pts), position FROM
(
    SELECT PoolTeam, Pts, "Skater" as position 
        FROM MondaySkaterStats WHERE Status = "True" AND WeekNumber = 1
    UNION ALL
    SELECT PoolTeam, Pts, "Goalie" as position 
        FROM MondayGoalieStats WHERE Status = "True" AND WeekNumber = 1
) table_alias

LEFT JOIN PoolTeams ON table_alias.PoolTeam = PoolTeams.PoolTeamID

GROUP BY PoolTeam, position;

Long term, it would help your database design to normalize these 2 tables into 1, possibly with an ancillary table for special goalie-only fields.

MondayPlayerStats     ExtraGoalieStats
-----------------     ----------------
Id                ->  MondayPlayerId
Pts                   Blocks
Status                ... whatever
WeekNumber
Position (goalie, etc)
... etc

edited Mar 21, 2018 at 17:12

answered Mar 21, 2018 at 16:34

willoller

7,3801 gold badge38 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

paparazzo Over a year ago

PoolTeamName is not available. You need a join.

willoller Over a year ago

Edited my answer to include a JOIN on the PoolTeams table

navaidstech Over a year ago

Thank you for this. Something very similar was suggested previously and I just might have to go with it. I would have thought joining the tables would be easy and yet it presents so many problems. Thank you for the help.

Collectives™ on Stack Overflow

SQL: Sum values from two different tables

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related