Skip to content
Advertisement

Select average entries per day into table

Ok I have looked at multiple entries in stackoverflow for this but cant seem to quite work them for me.

I have a table called email_Archive

It contains every email sent by every user.

Columns in Db are:

id
created
username
message
recipient

Challenge: I am trying to get the average amount of emails sent by a select list of users per day for the last month. (created covers YYYY-MM-DD:MM:ss)

select List of users:

A:
f:
g:
h:
i:
n:
q:
r:

The only info I want to return is userid and the average count per day for the last month only. and yes on some days that will be 0 but I need to show that 0 for that day

Advertisement

Answer

You can write

Select id as userid, Count(*)
From MessagesTable 
Where created > Dateadd(month, -1, GETDATE()) and id in (userids you want)
group by Cast(created as Date)

If you want it into a seperate table you can do either:

Select id as userid, Count(*)
into TargetTableName
From MessagesTable 
Where created > Dateadd(month, -1, GETDATE()) and id in (userids you want)
group by Cast(created as Date)

or

Insert into TargetTableName
Select id as userid, Count(*)
From MessagesTable 
Where created > Dateadd(month, -1, GETDATE()) and id in (userids you want)
group by Cast(created as Date)

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement