开发者

SQL Group By including empty rows

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.Da开发者_运维知识库te) < 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.


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


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


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)


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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜