Hi

iam having a problem with combining a few union statement together. The statements run separately without any problem.

The below union outputs the following results:

team points
leeds 3

``````select team ,points
from (
select hometeam team, sum(homepoints) points
from fixtures
where competitionname = 'prem'
group by hometeam
union
select awayteam team, sum(awaypoints) points
from fixtures
where competitionname = 'prem'
group by awayteam
) tmp
group by team``````

The below union outputs the following results:

team wins
leeds 1

``````select team, wins
from (
select hometeam team, count(*) wins
from fixtures
where competitionname = 'prem'and homepoints = '3'
group by hometeam
union
select awayteam team, count(*) wins
from fixtures
where competitionname = 'prem' and awaypoints = '3'
group by awayteam
) tmp
group by team``````

What I need to be able to do it see the following results but i am not sure how to do it:

team points wins
leeds 3 1

my table is structured like this:

homegoals awaygoals hometeam awayteam homepoints awaypoints competitionname

thanks for any help

anyone to help me please?

just added , wins*3 points in first line

``````select team, wins, wins*3 points
from (
select hometeam team, count(*) wins
from fixtures
where competitionname = 'prem'and homepoints = '3'
group by hometeam
union
select awayteam team, count(*) wins
from fixtures
where competitionname = 'prem' and awaypoints = '3'
group by awayteam
) tmp
group by team``````

Hello,

You problem is you need an AS entry to rename the variables. Try this:

``````select team , sum(points)
from (
select hometeam as team, sum(homepoints) as points
from fixtures
where competitionname = 'prem'
group by hometeam
union
select awayteam as team, sum(awaypoints) as points
from fixtures
where competitionname = 'prem'
group by awayteam
) tmp
group by team``````