mysql two tables with same entry name adding values

Question

I have a table that has basically a calendar entry for football games it looks like this

team1 || team1score || team2|| team2score || time || league

I want to update my positions table depending on entries from the calendar table.

so for example if I have in my table something like

Pats   || 14 || Saints || 3  || 1stleague
Cows   || 7  || falc   || 14 || 1stleague
Saints || 31 || falc   || 3  || 1stleague
Saints || 14 || cows   || 3  || 1stleague

I want a query that will first sum all the values in the team1score column with a distinct team1name. So for the table above it would give as a result

Pats   || 14
Cows   || 7
Saints || 45

then I want to do the same thing for team2 and team2score so the result would be

Saints || 3
falc   || 17
cows   || 3

then I want to join these two results and get:

Pats   || 14
Cows   || 10
Saints || 48
falc   || 17

I tried something like this

select distinct(t.team1), sum(t.team1score)
  from calendar t
  where league = '5a7'
  UNION
    select distinct(t2.team2), sum(t2.team2score)
    from calendar t2
    where league = '5a7'

but its not adding the same teams scores any ideas?

I know I'm not taking the sums when I do the union but I think I need to do the union to keep the values that don't have a matching team on the teams2 column. However I can't figure out how to do the union and sum at the same time.

nosid · Accepted Answer · 2012-04-12 22:21:12Z

2

Use a sub-query to create a union of the team1 and team2 columns, aggregate the scores (only) in the outer query:

SELECT LOWER(t.team) team, SUM(t.score) score
FROM (
    SELECT team1 team, team1score score
    FROM calendar
    WHERE league='5a7'
    UNION ALL
    SELECT team2, team2score
    FROM calendar
    WHERE league='5a7'
) t
GROUP BY 1

Edit: In a UNION, the names/aliases of the select statements after the union are ignored. You can add team and score, if you think it improves the readability. However, it is not required.

Edit: Added LOWER, i.e. group case-insensitive.

edited Apr 12, 2012 at 22:21

answered Apr 12, 2012 at 22:02

nosid

50.4k13 gold badges117 silver badges146 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user541597 Over a year ago

should there be a score by team2score?

user541597 Over a year ago

It is not adding the values from the different sides. For example in my example it's not adding cows 7 + cows 3.

Collectives™ on Stack Overflow

mysql two tables with same entry name adding values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related