SQL Query, Count two columns based on same id

Question

I have sql server databse with two tables, teams and games. Each game has a predicted_winner column and a winner column. both columns reference the team table id field. I want to query the database and get a count of how many games the team was predicted to win, and how many they actually won. However based on my query it is only returning a count of the predicted_winner or the winner, whichever column I join the team table on. how do I get accurate counts of both columns.

This is my incorrect query

SELECT teams.id, count(predicted_Winner), count(winner)
FROM teams left join games on teams.id=games.predicted_winner
GROUP BY teams.id

Provide us input/output, it would be nice to have a sqlfiddle link — neshkeev
– neshkeev, Commented Sep 23, 2014 at 21:45

Mureinik · Accepted Answer · 2014-09-23 21:50:40Z

1

Join the table twice, once for each column:

SELECT    teams.id, COUNT(pw.predicted_winner), COUNT(w.winner)
FROM      teams 
LEFT JOIN games pw ON teams.id = pw.predicted_winner
LEFT JOIN games w ON teams.id = w.winner
GROUP BY  teams.id

answered Sep 23, 2014 at 21:50

Mureinik

316k54 gold badges401 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

M.Ali · Accepted Answer · 2014-09-23 21:55:25Z

1

Since you have Two columns in games table referencing back to One column in Teams table,

you will need to join this table twice back to Teams table, once for each column referencing to teams table.

SELECT t.id
     , count(g1.predicted_Winner) AS predicted_Winner_count
     , count(g2.winner)           AS winner_count
FROM teams t
LEFT JOIN games g1 on t.id = g1.predicted_winner
LEFT JOIN games g2 on t.id = g2.winner
GROUP BY t.id

edited Sep 23, 2014 at 21:55

answered Sep 23, 2014 at 21:50

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Comments

neshkeev · Accepted Answer · 2014-09-23 22:03:24Z

0

Try this:

with results(game_id, predicted_Winner, winner) as (
  select 1, 1, 2  union all 
  select 2, 1, 2  union all 
  select 3, 1, 1  union all 
  select 4, 4, 4  union all 
  select 5, 5, 6  union all 
  select 6, 6, 6  union all 
  select 7, 3, 5  
), teams(id) as (
  select 1  union all 
  select 2  union all
  select 3  union all 
  select 4  union all 
  select 5  union all 
  select 6 
)
SELECT t.id team_id
     , count(case when predicted_winner = t.id then 1 end)
     , count(case when predicted_winner = t.id and predicted_winner = winner then 1 end)
  from results res
  right join teams t on t.id = res.predicted_winner or t.id = winner
 group by t.id
 order by 1

SQLFiddle

edited Sep 23, 2014 at 22:03

answered Sep 23, 2014 at 21:56

neshkeev

6,4863 gold badges32 silver badges49 bronze badges

Collectives™ on Stack Overflow

SQL Query, Count two columns based on same id

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related