I am having a MySQL table, called 'attendants' which is structured like below example (the real table is containing a lot more rows, but not important for this case):

I would like to get the sum of score per group. However, the group can exist either in column groupname1 or groupname2.

Looking at all GROUPA items in above example, I would like to get the sum output 40 (score of 10 in row 1, score of 10 in row 3 and score of 20 in row 5 > 10 10 20 = 40.

So in total the output should look like below:

I am trying to find my way in MySQL statements and know that I can use GROUP BY and SUM for that, but that would give me the sum per group, for the groupnames in one column, which is not what I want (this is what below statement would do).

SELECT groupname1, SUM(score) FROM attendants GROUP BY groupname1;

This is only giving me the score total for the groupnames in column groupname1, but the same groups can also exist in column groupname2 which I also would like to be added to the score total.

How could I get this arranged the way I want?

CodePudding user response：

Have two selects that alias the two columns groupname which you union together, then group that. Something like.

SELECT groupname, sum(score)
FROM
(
   SELECT groupname1 as groupname, score FROM attendants
   UNION ALL
   SELECT groupname2 as groupname, score FROM attendants
) as allattendants
GROUP BY groupname