0

table:

hometeam awayteam homepoints awaypoints

leeds man u 3 0
leeds man u 3 0
man u leeds 0 3

i want to calculate how many points each team has. with the above data leeds should have 9 and man u 0. i have come up wth the following sql code to work this out:

select hometeam, sum(homepoints)
from fixtures
group by hometeam
union
select awayteam, sum(awaypoints)
from fixtures
group by awayteam

this code almost does it but i get the following result:

hometeam sum(homepoints)
Leeds 6
Man Utd 0
Leeds 3

i need it to show each team only once(distinct) and sum the points. how do i get the following results:

hometeam sum(homepoints)
Leeds 9
Man Utd 0

any question please ask. thanks for your help

5
Contributors
7
Replies
8
Views
7 Years
Discussion Span
Last Post by tomato.pgn
0
select team ,sum(points) from 

(

select hometeam team, sum(homepoints) points
from fixtures
group by hometeam
union
select awayteam team, sum(awaypoints) points
from fixtures
group by awayteam

)
group by team

Edited by peter_budo: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)

0

that doesnt work. i get the following error:

#1248 - Every derived table must have its own alias

0

Try the following (added alias tmp for the derived table)

select team ,sum(points) from 
(
select hometeam team, sum(homepoints) points
from fixtures
group by hometeam
union
select awayteam team, sum(awaypoints) points
from fixtures
group by awayteam
) tmp
group by team
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.