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

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

that doesnt work. i get the following error:

#1248 - Every derived table must have its own alias

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

that works.
thank you very much

You are welcome.

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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.