Counting events per contributor per day
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');
and the output is this
Day Total
01 234
02 234
03 333
etc
So my problem is efficiency really we have a large number of contributors so I need to update the User_ID value in the current query for each month and run a number of times and then copy the data into excel
Pointers appreciated
Regards
John B
group-by count
asked Nov 25 '18 at 14:43
John BermanJohn Berman
134
add a comment |
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');
and the output is this
Day Total
01 234
02 234
03 333
etc
So my problem is efficiency really we have a large number of contributors so I need to update the User_ID value in the current query for each month and run a number of times and then copy the data into excel
Pointers appreciated
Regards
John B
group-by count
asked Nov 25 '18 at 14:43
John BermanJohn Berman
134
add a comment |
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');
and the output is this
Day Total
01 234
02 234
03 333
etc
So my problem is efficiency really we have a large number of contributors so I need to update the User_ID value in the current query for each month and run a number of times and then copy the data into excel
Pointers appreciated
Regards
John B
group-by count
asked Nov 25 '18 at 14:43
John BermanJohn Berman
134
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');
and the output is this
Day Total
01 234
02 234
03 333
etc
So my problem is efficiency really we have a large number of contributors so I need to update the User_ID value in the current query for each month and run a number of times and then copy the data into excel
Pointers appreciated
Regards
John B
group-by count
group-by count
asked Nov 25 '18 at 14:43
John BermanJohn Berman
134
asked Nov 25 '18 at 14:43
John BermanJohn Berman
134
asked Nov 25 '18 at 14:43
John BermanJohn Berman
134
asked Nov 25 '18 at 14:43
John BermanJohn Berman
134
asked Nov 25 '18 at 14:43
John BermanJohn Berman
134
134
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53468637%2fcounting-events-per-contributor-per-day%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53468637%2fcounting-events-per-contributor-per-day%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
