Issue with a union statement

[ryan1987]

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

[ryan1987]

anyone to help me please?

[urtrivedi]

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

[rch1231]

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