Problem with a SQL SELECT Query

Question

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

Spiny Norman · Accepted Answer · 2010-12-13 11:31:03Z

3

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

answered Dec 13, 2010 at 11:31

Spiny Norman

8,3271 gold badge34 silver badges57 bronze badges

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

3 Comments

user533832 Over a year ago

you are assuming id_game is unique - which of course it probably is but @Arturo doesn't specify

Spiny Norman Over a year ago

@JackPDouglas You're right, but you are assuming that referee1 != referee2 - which he also didn't specify ;)

user411103 Over a year ago

Thank you both, I am testing but it seems OK. Of course Id_game is unique and referee1 != referee2 in the same row. THANKS. I will tell you in a minute.

score 0 · Accepted Answer · 2010-12-13 12:01:04Z

0

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;

edited Dec 13, 2010 at 12:01

answered Dec 13, 2010 at 11:52

user533832

Comments

marc_s · Accepted Answer · 2010-12-13 12:19:54Z

0

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

edited Dec 13, 2010 at 12:19

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Dec 13, 2010 at 12:04

Angelin Nadar

9,39610 gold badges46 silver badges54 bronze badges

1 Comment

marc_s Over a year ago

If you post code or XML, please highlight those lines in the text editor and click on the "code" button (101 010) on the editor toolbar to nicely format and syntax highlight it!

score 0 · Accepted Answer · 2010-12-13 13:40:18Z

0

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

edited Dec 13, 2010 at 13:40

answered Dec 13, 2010 at 13:33

user411103

2 Comments

user411103 Over a year ago

In fact, the error I get is "#1248 - Every derived table must have its own alias". Any help?

user533832 Over a year ago

Some RDBMSs (eg MySQL, postgres) insist on an alias for each subquery, so select * from (select * from games) throw an error but select * from (select * from games) as g is fine.

Collectives™ on Stack Overflow

Problem with a SQL SELECT Query

4 Answers 4

3 Comments

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related