Monday, 12 August 2013

sql server 2012: syntax trouble with a multi select union query

sql server 2012: syntax trouble with a multi select union query

I'm struggling to nail down a sql server 2012 query I'm trying to write
and hoping somebody can help me out. This is what I currently have as my
query:
--home wins
select NULL as roadlosses, Count(t1.TeamName) as homewins, t1.TeamName
from scores s
inner join games g
on g.GameID=s.GameID
inner join teams t1
on t1.TeamID=g.HomeTeam
inner join teams t2
on t2.Teamid=g.AwayTeam
where (s.hometotalruns - s.awaytotalruns) > 0 and t1.TeamName = 'Pirates'
group by t1.teamname
UNION
--road losses
select Count(t2.TeamName) as roadlosses, NULL, t2.TeamName from scores s
inner join games g
on g.GameID=s.GameID
inner join teams t1
on t1.TeamID=g.HomeTeam
inner join teams t2
on t2.Teamid=g.AwayTeam
where (s.hometotalruns - s.awaytotalruns) > 0 and t2.TeamName = 'Pirates'
group by t2.TeamName
This gets me close with the results being:
roadlosses homewins teamname
NULL 41 Pirates
26 NULL Pirates
I want this to return only 1 row eliminating the NULLS but I just can't
seem to get it.
i tried looking at this but it's not quite exactly what I need.
Thanks in advance.

No comments:

Post a Comment