Ask the Expert

SUMs from either of two columns

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: