I have a table that stores the dates of events, and I would like to find the average number of events by the day of the week.

e.g.,

Result:

Wed: 2
Thu: 1
Fri: 1

UPDATE

I am told that I gave a poor example. Here is another attempt:

There was 1000 events in the month of June. Each event is a row with an ID and a date. There can be multiple events in a day (In the table below, imagine that there are 250 events on 9/1 and 150 on 9/2 and 300 on 9/3, etc.). I would like to know the average number of events for each day (Monday, Tuesday, Wednesday, etc.) for the entire month of June

Yes, I can simply divide by four to find the average for June but I am using a single month as an example. In reality, I am having to average out over several months and would like a solution where I am not having to determine the number of weeks between dates manually (e.g., using a date time calculator or counting the weeks with a calendar).

Here is a query I tried

SELECT COUNT(DATEPART(DD, EVENT_DATE) AS 'Event Total', AVG(COUNT(DATEPART(DD, EVENT_DATE)) AS 'Average'

FROM TABLE_1
GROUP BY DATEPART(DD, EVENT_DATE)

I'm having difficulty grouping by the days and then finding the average of the grouping. Is this possible with just t-sql?

CodePudding user response：

Use Day of Week to get your grouping and count(*) to count all occurrences for the grouping

declare @Tmp as table(ID int, date date)

Insert into @Tmp
(ID,    date)
values(1,'2021-09-01')
,(2,    '2021-09-01')
,(3,    '2021-09-02')
,(4,    '2021-09-03')

select count(*) freq,DatePart(WEEKDAY,date) DayOfWeek from @Tmp group by DatePart(WEEKDAY,date) 

CodePudding user response：

Use DATENAME to get the name of the week day, then LEFT to get the left-most three characters, group on this:

SELECT 
LEFT(DATENAME(dw, [date]), 3), 
COUNT([id]) 
FROM my_table 
GROUP BY 
LEFT(DATENAME(dw, [date]), 3);