title: How do I include empty rows in a single GROUP BY DAY(date_field) SQL query?
body: <p>I'm using MS SQL Server but welcome comparitive solutions from other databases.</p>
  
  <p>This is the basic form of my query.  It returns the number of calls per day from the 'incidentsm1' table:</p>
  
  <pre><code>SELECT 
    COUNT(*) AS "Calls",
    MAX(open_time),
    open_day
  FROM 
    (
  SELECT
   incident_id,
   opened_by,
   open_time - (9.0/24) AS open_time,
   DATEPART(dd, (open_time-(9.0/24))) AS open_day
     FROM incidentsm1 
     WHERE 
   DATEDIFF(DAY, open_time-(9.0/24), GETDATE())&lt; 7
  
    ) inc1
  GROUP BY open_day
  </code></pre>
  
  <p>This data is used to draw a bar graph, but if there were no calls on a given day of the week, there is no result row and thus no bar, and the user is like, "why does the graph only have six days and skip from Saturday to Monday?"</p>
  
  <p>Somehow I need to UNION ALL with a blank row from each day or something like that, but I can't figure it out.</p>
  
  <p>I am constrained to what I can do with one SQL statement and I have readonly access so I can't create a temporary table or anything.</p>