title: SQL Query Average by Day of Week
body: <p>I am trying to devise a query which will tell me the average number of procedures done on a given weekday as well as the total number of procedures on that week day for the entire time frame. The query I've developed looks like it works, but the values are not adding up correctly.</p>
  
  <pre><code>SELECT [Day], COUNT(*) AS "Week Day Count", AVG(Totals) AS [Avg]
  FROM
  (
      SELECT 
    w = DATEDIFF(WEEK, 0, CompleteDate),
        [Day]  = DATENAME(WEEKDAY, CompleteDate),
        Totals = COUNT(*)
      FROM dbo.[order]
        WHERE CompleteDate Between '2015-01-01' AND '2016-04-22'
              AND PlacerFld2 IN ('CT','SAMR')
              AND OrderStatusID = '2'
      GROUP BY 
          DATEDIFF(WEEK, 0, CompleteDate),
          DATENAME(WEEKDAY, CompleteDate),
          DATEPART(WEEKDAY, CompleteDate)
    ) AS q
  GROUP BY [Day]
  ORDER BY [Day];
  </code></pre>
  
  <p>I feel like the Average results are correct, however, the "Week Day Count" does not come up nearly as high as I thought it should be and perhaps it's just the way I am computing it.</p>
  
  <p>When I add up the values in the Week Day Count it comes up to be about 365, but when I do the query below, I get about 1750:</p>
  
  <pre><code>SELECT COUNT(*) AS "Total 2015-2016"
  FROM [order]
  WHERE CompleteDate Between '2015-01-01' AND '2016-04-22'
              AND PlacerFld2 IN ('CT','SAMR')
              AND OrderStatusID = '2'
  </code></pre>