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.

Query Help for Counting Events in a Day by each contributor

Discussion in 'MySQL' started by johnberman, Nov 25, 2018.

  1. #1
    Hi

    I have a data set that holds meteor detection data
    Each row is Unique Record and among other things it holds
    event_ID - Unique
    Date
    User_Id

    Im trying to write a query that will for a Given Year and Month count the events per day recorded by each contributor

    The following works
    SELECT
    DATE_FORMAT(date, '%d') AS DAY,
    COUNT(*) AS total
    FROM
    meteor_events
    where MONTHNAME(date) = 'August'
    AND
    Year(date) = '2018'
    AND meteor_events.user_ID = 13
    GROUP BY
    DATE_FORMAT(date, '%d');

    However only for a single contributor so I have to run it a number of times to get the daily totals for all the contributors - Help really appreciated

    Regards
    John B
     
    johnberman, Nov 25, 2018 IP
  2. shofstetter

    shofstetter Well-Known Member

    Messages:
    178
    Likes Received:
    7
    Best Answers:
    1
    Trophy Points:
    120
    #2
    Why not group by both the date and userid? something like this:
    SELECT
    DATE_FORMAT(date, '%d') AS DAY,
    COUNT(*) AS total
    meteor_events.user_ID
    FROM
    meteor_events
    where MONTHNAME(date) = 'August'
    AND
    Year(date) = '2018'
    AND = 13
    GROUP BY
    meteor_events.user_ID, DATE_FORMAT(date, '%d');
     
    shofstetter, Jan 31, 2019 IP