count events by user by day by month

Hi

I have a table called meteor_events
The primary key is: event_ID
Each user submits a number of event per month
Each event amongst other things has

event_ID - Primary Key
User_ID - this is a number and unique for each user
Date - in yyyy,mm,dd - Format

As an example in Jan 2017 on say the 1st

user 1 - 25 events
user 2 - 300 events

Im looking for a query that will look at each month and calculate how many events per user per day by month

thanks in advance


Regards
John B

 

Per day by month .. Is it the average you are looking for? In addition, can you by any chance share 1 real entry? Not all the nonsense that doesn't matter, but those core fields and values that you mentioned.

 

Thanks for the response so a bit more detail so you get the idea of the end game

The data is meteor detection by radio and each calendar month a number of contributors (User_ID) will provide data

now due to the way individal collection stats are set up soem are more sensative than others and as an example

User_ID 1 -

01 Jan - 70 events
02 Jan - 100 events
03 Jan - 65 events

User_ID 2 -

01 Jan - 200 events
02 Jan - 225 events
03 Jan - 196 events

Now there are times of the year when there are predicted metoer showers so you woudl expect the daily count to increase significanlty - thats expected

However there are are also times when there are no expected events that counts still go up because there are meteor showers that we are unaware of


Hope your with me so far

so if can get a query that would create a table called Jan_2017 and it included

day of month, user_id, and count per day per user and evetually plot that in excel I could see the unexpected peaks

ie if there is no expected shower but I get this data


User_ID 1 -

01 Jan - 70 events
02 Jan - 400 events
03 Jan - 65 events

User_ID 2 -

01 Jan - 200 events
02 Jan - 800 events
03 Jan - 196 events


You can see that on the 2 Jan there was an unexpected peak

attached is a sample extract

I could do this with excel but I guess a MySql Query would be so much quicker - there are around 100,000 events per month

Regards
John B

 

SELECT user_ID, MONTH(date), DATE(date), COUNT(*) FROM Table1 GROUP BY MONTH(date), DATE(date), user_ID

Code (markup):

user_ID    MONTH(date)    DATE(date)    COUNT(*)
3    4    2017-04-20    7
4    4    2017-04-20    1
2    4    2017-04-21    5
3    4    2017-04-21    50
4    4    2017-04-21    12
6    4    2017-04-21    6
3    5    2017-05-20    1

Code (markup):

Something like that ..

 

perfect thanks