6

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.

Improve this question
6
  • What value do you want to retrieve from the FooConfig table? It looks like you're doing nothing with it. Commented Jun 10, 2011 at 19:18
  • fc.Days, used in the WHERE clause Commented Jun 10, 2011 at 19:21
  • Oh yes - sorry! It's been a long day Commented Jun 10, 2011 at 19:23
  • 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? Commented Jun 10, 2011 at 19:27
  • FooType -> FooConfig is 1:0-1. And, you are correct about FooTypeID. Commented Jun 10, 2011 at 19:31

4 Answers 4

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

That's much simpler than the case statement I wrote. Can't believe I didn't think of that.
@Conrad, why whatever are you talking about?
@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.
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!
@Steve Just so you know I do prefer what you did
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
Improve this answer

4 Comments

It'll work but it's inefficient by comparison to the JOIN solution, is it not?
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...
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.
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?
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)
Improve this answer

Comments

0

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
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.