SQL Group By including empty rows

Question

For the sake of this question, let's suppose this table structure:

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 at 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 that have no people living there. Is there any way to make it count 0 instead?

(I'm targetting SQL Server 2005, in the off chance that it matters)

EDIT: Here's my real (anonymized) query, after 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

(The translation between my initial example and this is: Foo -> People, FooType -> Places, FooConfig -> A third table, for extra fun) I can make this work with Fosco's solution, but I'd prefer Steve's.

What value do you want to retrieve from the `FooConfig` table? It looks like you're doing nothing with it. — Steve Mayne, Jun 10 '11 at 19:18
I assume the `s.FooTypeID` is supposed to be `f.FooTypeID`, and that `st` is supposed to be `ft`? Is FooType -> FooConfig a 1:0-1, or a 1:m? — Steve Mayne, Jun 10 '11 at 19:27
FooType -> FooConfig is 1:0-1. And, you are correct about FooTypeID. — Mike Caron, Jun 10 '11 at 19:31

Steve Mayne · Accepted Answer · 2011-06-10T19:31:49.000

4

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'll drop the LEFT JOIN to that table (as it'll always be there). We can then include the extra criteria in the join to 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, then the extra date criteria can't be used (as they would always evaluate to false) - so we'd have 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

edited Jun 10 '11 at 19:31

answered Jun 10 '11 at 18:57

Steve Mayne

22,285
4
49
49

That's much simpler than the case statement I wrote. Can't believe I didn't think of that. – HLGEM Jun 10 '11 at 19:04
@Conrad, why whatever are you talking about? – Mike Caron Jun 10 '11 at 19:08
@Steve, the problem with your solution is that my real query is a lot more complicated (I'm joining in several tables to pull dates and stuff), and I can't get it to work. I will update my question with something a lot close to my real query, and maybe you can help. – Mike Caron Jun 10 '11 at 19:10
Your first revision is perfect! The Config column IS optional, but in that case I don't care about it anyway, so it works perfectly! – Mike Caron Jun 10 '11 at 19:41
@Steve Just so you know I do prefer what you did – Conrad Frix Jun 10 '11 at 19:42

score 2 · Answer 2 · answered Jun 10 '11 at 18:56

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

answered Jun 10 '11 at 18:56

Fosco

38,138
7
87
101

It'll work but it's inefficient by comparison to the `JOIN` solution, is it not? – Steve Mayne Jun 10 '11 at 19:03
Most excellent! I was able to adapt it to me real (much more complicated) query without any trouble, so I'm happy! Soon as I am able to accept... – Mike Caron Jun 10 '11 at 19:05
This would be a very poor performer (correlated subqueries operate row-by-agonizing-row and should be avoided) and @Steve Mayne has a better solution both from a maintainabilty and performance perspective. – HLGEM Jun 10 '11 at 19:06
Glad it could help. I also like Steve's solution... So many ways to get what you want with SQL. To the downvoter, you know it's wrong to downvote a working solution, right? – Fosco Jun 10 '11 at 19:07

score 0 · Answer 3 · answered Jun 10 '11 at 18:59

0

COUNT() counts the not NULL values. So you can code:

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

answered Jun 10 '11 at 18:59

Skrol29

5,402
1
20
25

Conrad Frix · Answer 4 · 2011-06-10T19:39:07.723

DateDiff(day, GetDate(), f.Date) > 0 AND DateDiff(day, GetDate(), f.Date) < fc.Days turns the LEFT JOINs into an INNER JOINs which isn't what you want.

To keep it a LEFT JOIN add or is null (or put the where clause into the JOIN as Steve Mayne 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

SQL Group By including empty rows

4 Answers4

Linked