SQL count record with zero relation

Question

I'm trying to find the id of the players whose name is 'Jack' and the number of games they have played:

Select p.id, count(*) as numOfGamePlayed
from player p, game g
where p.name = 'Jack' and p.id = g.id
group by p.id;

The problem is, this will only list the players name Jack who have played at least one game. How can I also list those who have not played any games?

Edit: for those player, the numOfGamePlayed must be 0. If I do this

Select p.id, count(*) as numOfGamePlayed
from player p LEFT JOIN game g ON p.id = g.id
where p.name = 'Jack'
group by p.id;

those who have not played any games will still have numOfGamePlayed displayed as 1

Abe Miessler · Accepted Answer · 2013-03-14T16:42:38.733

4

Stop using implicit joins for starters. This is a bad habit anyway. Then you can do this:

Select p.id, count(g.id) as numOfGamePlayed
from player p
LEFT JOIN game g
 ON p.id = g.id
where p.name = 'Jack'
group by p.id;

edited Mar 14 '13 at 16:42

answered Mar 14 '13 at 16:19

Abe Miessler

82,532
99
305
486

2

+1 Stop using implicit joins! – N West Mar 14 '13 at 16:20
but then, the numOfGamePlayed for that player who has not played any games is still displayed as 1, when it should be 0? – Chin Mar 14 '13 at 16:30
Try my updated version – Abe Miessler Mar 14 '13 at 16:42

score 0 · Answer 2 · answered Mar 14 '13 at 16:18

0

You need an outer join between player and game

SELECT p.ID, 
  COUNT(*) AS numOfGamePlayed
FROM player p
LEFT OUTER JOIN game g
ON p.id = g.id
WHERE p.name = 'Jack'
GROUP BY p.ID;

answered Mar 14 '13 at 16:18

A Hocevar

726
3
17

score 0 · Answer 3 · answered Mar 14 '13 at 16:19

0

SELECT p.id, count(*) as numOfGamePlayed
FROM player p
LEFT JOIN game g ON g.id = p.id
WHERE p.name = 'Jack'
--AND g.id IS NULL
GROUP BY p.id;

answered Mar 14 '13 at 16:19

T I

9,785
4
29
51

score 0 · Answer 4 · answered Mar 14 '13 at 16:53

You have to do count(game.PlayerId) instead of count(*)

See SQL Fiddle demo here

select p.PNAME, g.gameplayed
from player p 
left outer join game g
ON p.pid = g.pid;

Select p.pid, count(g.pid) as numOfGamePlayed
from player p 
LEFT JOIN game g 
ON p.pid = g.pid
where p.pname = 'Jack'
group by p.pid;

SQL count record with zero relation

4 Answers4