I am working on a side project in order to further my background in PL/SQL. The project I am currently working on involves stats from a online video game - Madden 19. I am trying to sum/aggregate all of the stats into one view/table. For example, I want to get the sum of all the stats for running backs over the course of the season. All my stats are segregated into 17 tables - one table for each week of the season but players only show up in the tables if they had stats in that week. So if they are benched or injured in week 1 and can not play in week 2, they will not be in the data for week 2 rushing stats.Example Data:
Stats Week 1:
Name | rushyds
-------------------
Coleman | 25
Henry | 15
Stats week 2:
Name | rushyds
-------------------
Coleman | 25
I can write a select query that can grab and sum the rushing stats for week 1 and week 2. My issue is that, say for example, a running back is injured by the end of the first week and accumulates stats in week 1 but can't play in week 2, the query will leave out the stats from week 1 because he does not show up in week 2. I believe this is the issue with the join as I am still learning this part of PL/SQL and that is why I am here. Here is my query as is -
select t.fullname,
sum(t.rushyds + t1.rushyds) as rushyds
from rstats_1 t,
rstats_2 t1
where t.rosterid = t1.rosterid
group by t.fullname
Query Result:
Name | rushyds
-------------------
Coleman | 50
Wanted Result:
Name | rushyds
-------------------
Coleman | 50
Henry | 15
Apologies if this has been asked before but no matter the way I word my string in google it is not coming up with the correct answer to this question as I probably do not know how to specifically ask this question. Thank you for your help in advance.
edit: I think I found something based on cases. I will try that then update this if it works
Update: Case didn't work but I think I figured out the issue using full outer join. I will have to use the Master sheet containing all names on the roster so that the group by uses those names instead of the names that only exist in week 1. I'll update my sheet with the final query once I am finished so that other can see what the solution was.
update 2: I figured out what to do, It involves a full outer join and nvl. this is the correct query as follows:
select r.fullname,
sum(nvl(t.rushyds,0) + nvl(t1.rushyds,0) as rushyds
from rosters r
full outer join rstats_1 t on r.rosterid = t.rosterid
full outer join rstats_2 t1 on r.rosterid = t1.rosterid
group by r.fullname
The full outer join will grab a name regardless if it shows up or not in the table by joining the unique identifier for the table - rosterId. The nvl statement handles the Null values that would result from this as the NULL value would overwrite the numbers populated leaving the value of the sum equal to null. By using NVL(column, number), it states, If this column turns up a null value, PL/SQL will populate this value with 'number' instead.
