title: Inserting missing entries when doing a GROUP BY?
body: <p>I am trying to use <code>GROUP BY</code> on my table to group my entries according to weeks. However, if there are no entries in certain week, how can I insert a 0 as opposed to a missing entry?</p>
  
  <pre><code>CREATE TABLE #TEMP (startdate datetime)
  
  INSERT INTO #TEMP VALUES('2011-02-01')
  INSERT INTO #TEMP VALUES('2011-02-02')
  INSERT INTO #TEMP VALUES('2011-02-03')
  INSERT INTO #TEMP VALUES('2011-02-04')
  INSERT INTO #TEMP VALUES('2011-02-05')
  INSERT INTO #TEMP VALUES('2011-02-18')
  INSERT INTO #TEMP VALUES('2011-02-19')
  INSERT INTO #TEMP VALUES('2011-02-20')
  INSERT INTO #TEMP VALUES('2011-02-21')
  
  SELECT DATEPART(YEAR,startdate) AS 'AYear',
         DATEPART(wk,startdate) AS 'AWeek',
         COUNT(*)
  FROM #TEMP
  GROUP BY DATEPART(YEAR,startdate),DATEPART(wk,startdate)
  ORDER BY 1,2
  
  DROP TABLE #TEMP
  </code></pre>
  
  <p>I am getting:</p>
  
  <pre><code>AYear   AWeek   (No column name)
  2011        6       5
  2011        8       2
  2011        9       2
  </code></pre>
  
  <p>but I want this:</p>
  
  <pre><code>AYear   AWeek   (No column name)
  2011        6       5
  2011        7       0
  2011        8       2
  2011        9       2
  </code></pre>
  
  <p>Any suggestions on how to do this?</p>