Count(*) allow group by null

Question

I have the following query:

select count(*) as memo_count, student_id from memo group by student_id

Which returns the following result set:

memo_count        student_id
3                 0
8                 1

However, what I would really like it to return is this:

memo_count        student_id
3                 0
8                 1
0                 2

What is happening is, the Group By is filtering out any count(*) which returns 0, this is not what I desire. Any way around this? Thanks.

Check out this: http://stackoverflow.com/questions/1980678/how-to-return-empty-groups-in-sql-group-by-clause — kol, Jun 17 '12 at 21:57
If it would return 0, that would imply that the `student_id` doesn't exist in this table. Do you have another table of student_id to left join against that would return all of them? — Michael Berkowski, Jun 17 '12 at 21:58
It can't count things that don't exist... do you want one record for every student? — Ry-, Jun 17 '12 at 21:58
Oops, I made a silly mistake, you were right Michael, there isn't any student_id of 2 in the memo table, that is why it wasn't working, I have resolved the problem, thanks! — user1462406, Jun 17 '12 at 22:05

score 2 · Answer 1 · answered Jun 17 '12 at 22:04

2

You can try with something like this. Assuming you have a table of students...

Select count(m.id), s.id from student s 
 left outer join memo m on m.student_id = s.id 
 group by s.id

answered Jun 17 '12 at 22:04

barsju

1 Answers1