Sum of score for each staff

ID	Staff1	Staff2	Staff1_Score	Staff2_Score	Total_Score
1	Sam	Joe	60	60	120
2	Sam	NULL	120	NULL	120
3	Joe	Sam	40	40	80
4	Joe	NULL	90	NULL	90
5	Sam	Joe	60	60	120
6	Sam	NULL	100	NULL	100
7	Joe	NULL	150	NULL	150

I have a table like that, this for record staff name and score.

But how can i find the sum of each staff score ?

I can't use sum() to find it out.

select sum(Staff1_Score)+sum(Staff2_Score) from table where Staff1='Sam'

But the result is wrong.

In table, Sam = 380 Joe = 400

Thanks for helping to solve this.

Dear

ID	Date	Staff1	Staff2	Staff1_Score	Staff2_Score	Total_Score
1	01-05-2023	Sam	Joe	60	60	120
2	06-05-2023	Sam	NULL	120	NULL	120
3	08-05-2023	Sam	NULL	80	NULL	80
4	12-05-2023	Joe	Sam	90	90	180
5	14-05-2023	Joe	NULL	60	NULL	60
6	15-05-2023	Sam	Joe	100	100	200
7	16-05-2023	Joe	NULL	150	NULL	150

If I need to count with a period. so how to do that? example : 1-05-2023 to 12-05-2023

Solution

You could create a query that combines the Staff1 and Staff2 and corresponding score.
After that, query that result summing the scores.
Something like this:

SELECT   StaffName,
         SUM(StaffScore)
FROM     (SELECT Staff1                    AS StaffName,
                 COALESCE(Staff1_Score, 0) AS StaffScore,
                 DateColumn
          FROM   table
          WHERE  Staff1 IS NOT NULL
          UNION ALL
          SELECT Staff2                    AS StaffName,
                 COALESCE(Staff2_Score, 0) AS StaffScore,
                 DateColumn
          FROM   table
          WHERE  Staff2 IS NOT NULL
         ) AS UnitedStaffs
WHERE    DateColumn BETWEEN CAST('01-05-2023' AS date) AND CAST('12-05-2023' AS date)
GROUP BY StaffName;

I can't imagine the column in your table that reports the date is actually called date. Please adjust as necessary.
Furthermore, the CAST may need adaption based on your international settings.