CODEWITHSUNDEEP
mysql
Cameron Swyft
Cameron Swyft

Reputation: 458

Counting all the wins in two columns using two columns?

Let's take this table for an example...

m_tid | m_tid2 | m_hteam_score | m_ateam_score
  2       5           69             30
  5       2           0               5

I'm bad at custom making tables, sorry...

So let's take this data, now m_tid and m_tid2 are columns for TID's that are in a separate table of their own.

Now what I want to do, is collect the score for team id2 (or team id1) of all the scores... How would I count two columns for whether or not the team is on m_tid and m_tid2

I don't have a query made, but I wouldn't know how I would go about making a query for this anyways. :(

The expected results would be something like this

  m_tid | m_tid_score | m_tid2 | m_tidscore2
    5         35          2           69

Upvotes: 1

Views: 29

Answers (2)

sgeddes
sgeddes

Reputation: 62831

Here's another option using conditional aggregation:

select o.id, sum(case when y.m_tid = o.id then y.m_hteam_score 
                 when y.m_tid2 = o.id then y.m_ateam_score
                 else 0 end) score
from othertable o 
    join yourtable y on o.id in (y.m_tid, y.m_tid2)
group by o.id

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269593

If you want to get the total score for each team, here is one method using correlated subqueries:

select t.*,
       (coalesce((select sum(s.m_hteam_score) from scores s where s.m_tid = t.tid), 0) +
        coalesce((select sum(s.m_ateam_score) from scores s where s.m_tid2 = t.tid), 0)
       ) as totalscore           
from teams t;

Upvotes: 1

Related Questions