SQL Group Including Empty Rows

Question

For this question, suppose this table structure is:

People: PersonID int PK Name varchar(50) Place int NULL FK -> Places.PlaceID MovedIn datetime Places: PlaceID int PK Name varchar(50) 

I want to determine how many people live in each place:

 SELECT pla.PlaceID, COUNT(*) FROM Places AS pla LEFT JOIN People as peo ON peo.PlaceID = pla.PlaceID GROUP BY pla.PlaceID 

This query will omit places where there are no people living there. Is there any way to do this count 0 instead?

(I am targeting SQL Server 2005, in case this is important)

EDIT: Here is my real (anonymous) request, trying to adapt Steve's solution:

 SELECT ft.FooTypeID, COUNT(f.FooID) FROM FooType as ft LEFT OUTER JOIN Foo f ON ft.FooTypeID = f.FooTypeID LEFT JOIN FooConfig fc ON ft.NotificationConfigID = fc.FooConfigID WHERE DateDiff(day, GetDate(), f.Date) > 0 AND DateDiff(day, GetDate(), f.Date) < fc.Days GROUP BY ft.FooTypeID 

(Translation between my original example and the following: Foo → People, FooType → Places, FooConfig → Third table, for added pleasure) I can do this work with the Fosco solution, but I would prefer Steve.

Answer 1

 SELECT pla.PlaceID, COUNT(peo.PersonID) FROM Places AS pla LEFT OUTER JOIN People as peo ON peo.PlaceID = pla.PlaceID GROUP BY pla.PlaceID 

EDITED question:

Assuming there is always a FooConfig entry, we put the LEFT JOIN in this table (since it will always be there). Then we can include additional criteria in the join in the Foo table:

 SELECT ft.FooTypeID, COUNT(f.FooID) FROM FooType as ft JOIN FooConfig fc ON ft.NotificationConfigID = fc.FooConfigID LEFT OUTER JOIN Foo f ON ft.FooTypeID = f.FooTypeID AND DateDiff(day, GetDate(), f.Date) > 0 AND DateDiff(day, GetDate(), f.Date) < fc.Days GROUP BY ft.FooTypeID 

If the FooConfig table is optional, additional date criteria cannot be used (since they will always be evaluated as false), so we would need to do something like:

 SELECT ft.FooTypeID, COUNT(f.FooID) FROM FooType as ft LEFT OUTER JOIN FooConfig fc ON ft.NotificationConfigID = fc.FooConfigID LEFT OUTER JOIN Foo f ON ft.FooTypeID = f.FooTypeID AND ( (DateDiff(day, GetDate(), f.Date) > 0 AND DateDiff(day, GetDate(), f.Date) < fc.Days) OR (fc.Days IS NULL) ) GROUP BY ft.FooTypeID 

Answer 2

You can use a column query

 select pla.PlaceID, ISNULL((select COUNT(*) from People where PlaceID = pla.PlaceID),0) as peopleCount from Places as pla order by PlaceID 

Answer 3

COUNT () counts non-null values. So you can encode:

 SELECT pla.PlaceID, COUNT(peo.PlaceID) As nbr FROM Places AS pla LEFT JOIN People AS peo ON (peo.PlaceID = pla.PlaceID) 

Answer 4

DateDiff(day, GetDate(), f.Date) > 0 AND DateDiff(day, GetDate(), f.Date) < fc.Days turns LEFT JOINs into INNER JOINs that you don't need.

To save a LEFT JOIN add or null (or put a where clause in a JOIN, as Steve Maine did)

 SELECT ft.FooTypeID, COUNT(f.FooID) FROM FooType as ft LEFT OUTER JOIN Foo f ON st.FooTypeID = s.FooTypeID LEFT JOIN FooConfig fc ON st.NotificationConfigID = fc.FooConfigID WHERE (DateDiff(day, GetDate(), f.Date) > 0 or f.Date IS NULL) AND (DateDiff(day, GetDate(), f.Date) < fc.Days or fc.Days Is NULLL or f.Date Is NULL ) GROUP BY ft.FooTypeID