total a union column

[ryan1987]

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

[urtrivedi]

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

[ryan1987]

that doesnt work. i get the following error:

#1248 - Every derived table must have its own alias

[mwasif]

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

[ryan1987]

that works.
thank you very much

[mwasif]

You are welcome.

[webdesignservic]

mwasif...you are great, the query work perfectly...you save my day...thanks...

[tomato.pgn]

if ur done(ur problem solved) then make this thread solved.....