CODEWITHSUNDEEP
sql
user411103
user411103

Reputation:

Problem with a SQL SELECT Query

I have a table GAMES with this information:

- Id_Game (int)
- Id_Referee1 (int)
- Id_Referee2 (int)

These are some rows:

ID_GAME    ID_REFEREE1    ID_REFEREE2
1          1000           728
2          1004           813
3          728            1004
4          1004           1000

I want to have a list with the number of games that a referee has officiated, doesn't matter if as a Referee1 or Referee2. In this case, I would like to receive this list order by number of games DESC.

REFEREE    NUMBER OF GAMES
1004       3
728        2
1000       2
813        1

Which would be the SQL query? I have no idea of how to join fields Id_Referee1 and Id_Referee2...

THANKS

Upvotes: 2

Views: 261

Answers (4)

user411103
user411103

Reputation:

I think something is wrong because I get an error with this query:

SELECT Referee, count(*) FROM 
(
  (SELECT Id_Game, Id_Referee1 as Referee FROM Games) 
  UNION ALL 
  (SELECT Id_Game, Id_Referee2 as Referee FROM Games)
) GROUP BY Referee

Can you see what is wrong? THANKS

Upvotes: 0

Angelin Nadar
Angelin Nadar

Reputation: 9300

SELECT ID_REFEREE1, count( * ) as no_of_games
FROM games
GROUP BY ID_REFEREE1
ORDER BY 2

Upvotes: 0

user533832
user533832

Reputation:

assuming id_referee1!=id_referee2 for all possible rows (probably using a check constraint):

select referee, count(*) as number_of_games 
from( select id_referee1 as referee from games 
      union all 
      select id_referee2 from games )
group by referee
order by count(*) desc;

if you don't want to make that assumption:

select referee, count(*) as number_of_games 
from( select id_referee1 as referee from games 
      union all 
      select id_referee2 
      from games 
      where id_referee1 is null or id_referee1!=id_referee2 )
group by referee
order by count(*) desc;

Upvotes: 0

Spiny Norman
Spiny Norman

Reputation: 8327

I think:

select referee, count(*) from (
    select id_game, id_referee1 as referee
    from games
    union
    select id_game, id_referee2 as referee
    from games
)
group by referee

Upvotes: 3

Related Questions