Select count with 0 count

问题:

Lets say I have following query:

SELECT top (5) CAST(Created AS DATE) as DateField, 
  Count(id) as Counted
FROM Table
GROUP BY CAST(Created AS DATE)
order by DateField desc

Lets say it will return following data set

DateField   Counted
2016-01-18  34
2016-01-17  99
2016-01-14  1
2015-12-28  1
2015-12-27  6

But when I have Counted = 0 for certain Date I would like to get that in result set. So for example it should look like following

DateField   Counted
2016-01-18  34
2016-01-17  99
2016-01-16  0
2016-01-15  0
2016-01-14  1

Thank you!

回答1:

Expanding upon KM's answer, you need a date table which is like a numbers table. There are many examples on the web but here's a simple one.

CREATE TABLE DateList (
 DateValue DATE,
 CONSTRAINT PK_DateList PRIMARY KEY CLUSTERED (DateValue)
 )
 GO
 -- Insert dates from 01/01/2015 and 12/31/2015
 DECLARE @StartDate DATE = '01/01/2015'
 DECLARE @EndDatePlus1 DATE = '01/01/2016'
 DECLARE @CurrentDate DATE = @StartDate

 WHILE @EndDatePlus1 > @CurrentDate
    BEGIN
    INSERT INTO DateList VALUES (@CurrentDate)
    SET @CurrentDate = DATEADD(dd,1,@CurrentDate)
    END

Now you have a table

then you can rewrite your query as follows:

SELECT top (5)  DateValue, isnull(Count(id),0) as Counted
FROM DateList 
LEFT OUTER JOIN Table
  on DateValue = CAST(Created AS DATE) 
GROUP BY DateValue
order by DateValue desc

Two notes: You'll need a where clause to specify your range. A join on a cast isn't ideal. The type in your date table should match the type in your regular table.

回答2:

One more solution as a single query:

;WITH dates AS 
(
  SELECT CAST(DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY [object_id]) - 1, '2016-01-14') as date) 'date' 
  FROM sys.all_objects
) 
SELECT TOP 5 
      [date] AS 'DateField', 
      SUM(CASE WHEN Created IS NULL THEN 0 ELSE 1 END) AS 'Counted'
  FROM dates
  LEFT JOIN Table ON [date]=CAST(Created  as date)
GROUP BY [date]
ORDER BY [date]

回答3:

For a more edgy solution, you could use a recursive common table expression to create the date list. PLEASE NOTE: do not use recursive common table expressions in your day job! They are dangerous because it is easy to create one that never terminates.

    DECLARE @StartDate date = '1/1/2016';
    DECLARE @EndDate date = '1/15/2016';

    WITH DateList(DateValue)
    AS
    (
            SELECT DATEADD(DAY, 1, @StartDate)
            UNION ALL
            SELECT DATEADD(DAY, 1, DateValue)
            FROM DateList
            WHERE DateList.DateValue < @EndDate
    )
    SELECT DateValue, isnull(Count(id),0) as Counted
    FROM DateList 
    LEFT OUTER JOIN [Table]
      ON DateValue = CAST(Created AS DATE) 
    GROUP BY DateValue
    ORDER BY DateValue DESC