I have a table with four fields: TeamHost, TeamAway, PointsHost, PointsAway. A sample of such data are teams from Bundesliga:
TeamHost TeamAway PointsHost PointsAway Stuttgart Dortmund 3 0 Dortmund Stuttgart 1 1
I want to produce a query that can display the fields Team, TotalPoints like:
Team Totalpoints Stuttgart 4 Dortmund 1
Please kindly rescue me.
Requires Free Membership to View
It's easy to obtain separate home and away totals for each team based on whether the team is TeamHost or TeamAway. The following two queries will accomplish this:
select TeamHost as Team
, Sum(PointsHost) as SubTotal
from matches
group by TeamHost
select TeamAway as Team
, Sum(PointsAway) as SubTotal
from matches
group by TeamAway
Now all we need to do is add these two subtotals together.
select Team
, sum(SubTotal) as Totalpoints
from (
select TeamHost as Team
, Sum(PointsHost) as SubTotal
from matches
group by TeamHost
union all
select TeamAway as Team
, Sum(PointsAway) as SubTotal
from matches
group by TeamAway
) as U
group by Team
The outer SELECT combines the two subtotals into one total for each team.
Alternatively, we could have used a JOIN here instead, because there is only one row per subtotal per team.
select H.Team
, H.SubTotal
+A.SubTotal as Totalpoints
from (
select TeamHost as Team
, Sum(PointsHost) as SubTotal
from matches
group by TeamHost
) as H
inner
join (
select TeamAway as Team
, Sum(PointsAway) as SubTotal
from matches
group by TeamAway
) as A
on H.Team = A.Team
My preference, however, is for the UNION. It seems to me to be a more natural approach to this type of problem.
This was first published in February 2005
Join the conversationComment
Share
Comments
Results
Contribute to the conversation