Q

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.


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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close