?
Solved

How to return a row with a zero count

Posted on 2008-10-16
9
Medium Priority
?
531 Views
Last Modified: 2012-05-05
I am trying to return a result that shows for each month between a set of dates a count of associated records for each of the months.

The query I have written works fine except that it doesn't include any months with zero records. If the query was involving a join then I believe I could use a LEFT JOIN to ensure that all records are returned, however this query is just on 1 table.

Does anyone know how I can include rows with no records?

The kind of result I would want would be:

January 2008            6
February 2008          3
March 2008               0
April 2008                  2

With my current SQL the March row would not be returned because it has a zero count.

Many thanks,
SELECT DATENAME(Month,DateAdded) AS 'Month', Year(DateAdded) As 'Year', Count(id) As 'Total'
FROM Candidate
WHERE (dateAdded >= '10/01/2007') and (dateAdded <= '05/30/2008')
GROUP BY Year(DateAdded), month(DateAdded), DATENAME(Month,DateAdded)

Open in new window

0
Comment
Question by:robert_raw
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22732916
The typical approach is to have a "calendar" table with all of the dates in it.  Your query then becomes:


SELECT DATENAME(Month, c.[Date]) AS 'Month', Year(c.[Date]) As 'Year', Count(d.id) As 'Total'
FROM Calendar c LEFT JOIN Candidate d ON c.[Date] = d.DateAdded
WHERE (c.[Date] >= '10/01/2007') and (c.[date] <= '05/30/2008')
GROUP BY DATENAME(Month, c.[Date]), Year(c.[Date])
0
 

Author Comment

by:robert_raw
ID: 22732981
Thanks for a quick reply. Would this approach require the calendar table to be managed to always ensure that the correct dates are stored in the table?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22733148
change count(id) to count(*) your were counting distinct ID's rather than records, however, for there to be a zero count, then, there cannot be any rows...

maybe you want to just count if between those dates ?

in which case:


SELECT DATENAME(Month,DateAdded) AS 'Month', Year(DateAdded) As 'Year', Count(case when (dateAdded >= '10/01/2007') and (dateAdded <= '05/30/2008')
 then 1 else 0 end) As 'Total'
FROM Candidate
GROUP BY Year(DateAdded), month(DateAdded), DATENAME(Month,DateAdded)

0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 22733516
robert_raw said:
>>Thanks for a quick reply. Would this approach require the calendar table to be managed to always ensure
>>that the correct dates are stored in the table?

That depends.  One approach would be to have the calendar table contain every date between, say, 1 Jan 1950
and 31 Dec 2100; for almost all purposes, that would probably be sufficient.  Another approach would be to build
it dynamically:

DECLARE @start datetime, @end datetime, @counter datetime
SET @start = '1 Oct 2007'
SET @end = '30 May 2008'
SET @counter = @start

CREATE TABLE #calendar ([Date] datetime)

WHILE @counter <= @end BEGIN
    INSERT INTO #calendar ([Date]) VALUES (@counter)
    SET @counter = DATEADD(d, 1, @counter)
END

SELECT DATENAME(Month, c.[Date]) AS 'Month', Year(c.[Date]) As 'Year', Count(d.id) As 'Total'
FROM #calendar c LEFT JOIN Candidate d ON c.[Date] = d.DateAdded
WHERE (c.[Date] >= '10/01/2007') and (c.[date] <= '05/30/2008')
GROUP BY DATENAME(Month, c.[Date]), Year(c.[Date])

DROP TABLE #calendar
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22734645
Or as a function:

which once built you can simply use it in a select statement such as

select datename(mm,period),year(period), counter as total
from dbo.uGet_Monthly_Candidates('01 Oct 2007','30 May 2008')
order by period

but create it first (a once-off exercise, once created can re-use as often as needed)...


create function uGet_Monthly_Candidates( @start datetime, @end datetime)
returns @tbl table (period datetime, counter int)
as
begin
 
declare @calendar table ([Date] datetime)
 
WHILE @start <= @end BEGIN
    INSERT INTO @calendar values (@start)
    SET @start = dateadd(mm,1,@start)
END
 
INSERT @tbl 
SELECT convert(varchar(6),c.[Date],112)+'01', Count(id)
FROM @calendar c LEFT JOIN Candidate d ON c.[Date] = convert(varchar(8),d.DateAdded,112)
GROUP BY convert(varchar(6),c.[Date],112)+'01'   -- just make it yyyymm01
 
return
END
GO

Open in new window

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 22735401
I suggest a more generic approach, creating a table of sequential numbers, which will be used as a date offset to a specified starting date.  I'll post code in the next post to create the seq nums table.

The main code would then be something like this:

SELECT DATENAME(Month,date) AS 'Month', Year(date) As 'Year', Count(id) As 'Total'
FROM (
    SELECT DATEADD(MONTH, seqNum, CAST('10/01/2007' AS DATETIME)) AS date
    FROM seqNums
    WHERE DATEADD(MONTH, seqNum, CAST('10/01/2007' AS DATETIME)) BETWEEN '10/01/2007' AND '05/30/2008'
) AS dates
LEFT JOIN Candidate ON dateAdded >= date AND dateAdded < DATEADD(MONTH, 1, date)
ORDER BY [Year], [Month]
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 22735403
DECLARE @maxValueToGenerate INT

-- chg next value to the highest value you need;
-- the current code supports up to 10M - 1, but you could increase that if you wanted to

SET @maxValueToGenerate = 16000

IF OBJECT_ID('seqNums') IS NOT NULL
    DROP TABLE seqNums

CREATE TABLE seqNums (
    seqNum INT,
    CONSTRAINT seqNums_CI --don't remove, improves performance!
        UNIQUE CLUSTERED (seqNum) WITH FILLFACTOR = 100
    )

INSERT INTO seqNums
SELECT [1s] + [10s] + [100s] + [1000s] + [10Ks] + [100Ks] +
      [1Ms] + [10Ms]
FROM (
    SELECT 0 AS [1s] UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
    SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) AS digits
CROSS JOIN (
    SELECT 00 AS [10s] UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL
    SELECT 30 UNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60 UNION ALL
    SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90
) AS [10s]
CROSS JOIN (
      SELECT [100s]
      FROM (
    SELECT 000 AS [100s] UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL
    SELECT 300 UNION ALL SELECT 400 UNION ALL SELECT 500 UNION ALL SELECT 600 UNION ALL
    SELECT 700 UNION ALL SELECT 800 UNION ALL SELECT 900
      ) inlineData
      WHERE [100s] <= @maxValueToGenerate
) AS [100s]
CROSS JOIN (
      SELECT [1000s]
      FROM (
    SELECT 0000 AS [1000s] UNION ALL SELECT 1000 UNION ALL SELECT 2000 UNION ALL
    SELECT 3000 UNION ALL SELECT 4000 UNION ALL SELECT 5000 UNION ALL SELECT 6000 UNION ALL
    SELECT 7000 UNION ALL SELECT 8000 UNION ALL SELECT 9000
      ) inlineData
      WHERE [1000s] <= @maxValueToGenerate
) AS [1000s]
CROSS JOIN (
      SELECT [10Ks]
      FROM (
    SELECT 00000 AS [10Ks] UNION ALL SELECT 10000 UNION ALL SELECT 20000 UNION ALL
    SELECT 30000 UNION ALL SELECT 40000 UNION ALL SELECT 50000 UNION ALL SELECT 60000 UNION ALL
    SELECT 70000 UNION ALL SELECT 80000 UNION ALL SELECT 90000
      ) inlineData
      WHERE [10Ks] <= @maxValueToGenerate
) AS [10Ks]
CROSS JOIN (
      SELECT [100Ks]
      FROM (
    SELECT 000000 AS [100Ks] UNION ALL SELECT 100000 UNION ALL SELECT 200000 UNION ALL
    SELECT 300000 UNION ALL SELECT 400000 UNION ALL SELECT 500000 UNION ALL SELECT 600000 UNION ALL
    SELECT 700000 UNION ALL SELECT 800000 UNION ALL SELECT 900000
      ) inlineData
      WHERE [100Ks] <= @maxValueToGenerate
) AS [100Ks]
CROSS JOIN (
      SELECT [1Ms]
      FROM (
    SELECT 0000000 AS [1Ms] UNION ALL SELECT 1000000 UNION ALL SELECT 2000000 UNION ALL
    SELECT 3000000 UNION ALL SELECT 4000000 UNION ALL SELECT 5000000 UNION ALL SELECT 6000000 UNION ALL
    SELECT 7000000 UNION ALL SELECT 8000000 UNION ALL SELECT 9000000
      ) inlineData
      WHERE [1Ms] <= @maxValueToGenerate
) AS [1Ms]
CROSS JOIN (
      SELECT [10Ms]
      FROM (
    SELECT 00000000 AS [10Ms] UNION ALL SELECT 10000000 UNION ALL SELECT 20000000 UNION ALL
    SELECT 30000000 UNION ALL SELECT 40000000 UNION ALL SELECT 50000000 UNION ALL SELECT 60000000 UNION ALL
    SELECT 70000000 UNION ALL SELECT 80000000 UNION ALL SELECT 90000000
      ) inlineData
      WHERE [10Ms] <= @maxValueToGenerate
) AS [10Ms]
WHERE [1s] + [10s] + [100s] + [1000s] + [10Ks] + [100Ks] + [1Ms] + [10Ms] <= @maxValueToGenerate
ORDER BY [1s] + [10s] + [100s] + [1000s] + [10Ks] + [100Ks] + [1Ms] + [10Ms]

DBCC SHOWCONTIG(seqNums) --make sure table is highly contig for max performance

SELECT COUNT(*) FROM seqNums --verify that the expected number of rows were generated
0
 

Author Closing Comment

by:robert_raw
ID: 31506816
Thanks matthewspatrick, the dynamic calendar table worked great. Many thanks.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22739102
interesting, may I suggest you look at the date join in your selected answer. if it is a datetime in the candidate table, and there are time components, then you may want to address that. May I enquire if you looked at / tried my posting ? please do not get me wrong, it is just a matter of curiosity and interest.
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question