SQL Group By including empty rows

sql sql-server group-by

15,008

Solution 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'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

Solution 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

15,008

Author by

Mike Caron

My languages: public class MikeCaron { public override ToString() { return "Just some guy :)"; } public IEnumerable<string> Tags { get { return new [] { "#SOreadytohelp" }; } } } And: @interface MikeCaron : NSObject @property (readonly) NSString * description; @end @implementation MikeCaron -(NSString*)description { return @"Just some guy :)"; } @end

Updated on June 28, 2022

Comments

Mike Caron almost 2 years
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.
Steve Mayne almost 13 years

It'll work but it's inefficient by comparison to the JOIN solution, is it not?
dburges almost 13 years

That's much simpler than the case statement I wrote. Can't believe I didn't think of that.
Mike Caron almost 13 years

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...
dburges almost 13 years

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.
Fosco almost 13 years

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?
Mike Caron almost 13 years

@Conrad, why whatever are you talking about?
Mike Caron almost 13 years

@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 almost 13 years

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!
Conrad Frix almost 13 years

@Steve Just so you know I do prefer what you did