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

3
Contributors
3
Replies
4
Views
8 Years
Discussion Span
Last Post by rch1231

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
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.