1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

count events by user by day by month

Discussion in 'Databases' started by johnberman, Jul 30, 2017.

  1. #1
    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
    SEMrush
     
    johnberman, Jul 30, 2017 IP
    SEMrush
  2. Blank ™

    Blank ™ Well-Known Member

    Messages:
    223
    Likes Received:
    18
    Best Answers:
    6
    Trophy Points:
    110
    #2
    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.
     
    Blank ™, Jul 30, 2017 IP
  3. johnberman

    johnberman Peon

    Messages:
    15
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #3
    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
     

    Attached Files:

    johnberman, Jul 30, 2017 IP
  4. Blank ™

    Blank ™ Well-Known Member

    Messages:
    223
    Likes Received:
    18
    Best Answers:
    6
    Trophy Points:
    110
    #4
    
    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 ..
     
    Blank ™, Jul 30, 2017 IP
  5. johnberman

    johnberman Peon

    Messages:
    15
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #5
    perfect thanks
     
    johnberman, Jul 30, 2017 IP