Daniel Underwood Daniel Underwood - 5 months ago 11
MySQL Question

sum two numbers in separate columns by unique id

I have four columns home_id_1, home_score_1, away_id_1, away_score_1

I'm trying to sum up the scores for home_score_1 AND away_score_1 where, for instance, the home_id_1 AND away_id_1 columns both return 1.

Could someone please assist as I can't figure this one out.

The closest I've gotten to it is:

SELECT home_score_1 AS home FROM scores
WHERE home_id_1 = 1
UNION
SELECT away_score_1 AS home FROM scores
WHERE away_id_1 = 1


but this only provides the two separate entries and no sum total.

home_id_1 : home_score_1 : away_id_1 : away_score_1

1 : 3 : 2 : 8

2 : 4 : 1 : 7

Sorry, I don't know how to make the above columns format properly. So, looking to find both home/away IDs that show 1, and return a sum against that ID from the score columns to give a total of 3+7.

Thanks.

Answer

just wrap your code as sub query and it should be work

    SELECT SUM(a.home) as home FROM
    ( 
      SELECT home_score_1 as home FROM scores
      WHERE home_id_1 = 1
      UNION
      SELECT away_score_1 AS home FROM scores
      WHERE away_id_1 = 1 
     ) a