Daniël Cronk Daniël Cronk - 7 months ago 28
SQL Question

SQL Query Average by Day of Week

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.

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];


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.

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:

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'

Answer

I suspect that you actually want the sum of the total:

SUM(Totals) AS "Week Day Count"

Your query is (I think) counting the number of days in the data for each weekday.