-1
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

Raymond
  • 3
  • 3
  • 1
    What does this mean: "I can't use sum"? What exactly have you tried and what didn't work as expected? Please add the outcome you want to get. – Jonas Metzler Jun 28 '23 at 08:47
  • Is the 5th column meant to be staff2_score? – NickW Jun 28 '23 at 08:49
  • select sum(Staff1_Score)+SUM(Staff2_Score) FROM table where Staff1='Sam' – Raymond Jun 28 '23 at 08:51
  • Yes, I correct it – Raymond Jun 28 '23 at 08:55
  • Your actual problem is one of table design, your data should be properly normalised with a single column for the Staff name and score, then you could easily *sum()* your values. Fix that first before you get too far down a rabbit hole - what happens when you have 50 staff/score columns....? – Stu Jun 28 '23 at 08:56
  • Why do you want to sum Staff1_Score and Staff2_Score although your table already has the column Total_Score with exactly this sum? Please show first your sample data as table and then the expected result as another table to prevent confusion. Your query returns 460 as sum for Sam, which is correct. See https://dbfiddle.uk/xX1Ar6HW – Jonas Metzler Jun 28 '23 at 08:59
  • @Raymond https://meta.stackoverflow.com/questions/388759/why-should-i-tag-my-rdbms – Reporter Jun 28 '23 at 09:06
  • Because my primary key is id, ID is our job no – Raymond Jun 28 '23 at 10:32
  • Please tag your question with the database you are running: mysql, oracle, sql-server... Depending on your database, different solutions may be available. – GMB Jun 28 '23 at 14:18

1 Answers1

1

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.

Robert Kock
  • 5,795
  • 1
  • 12
  • 20