Find teacher monthly report based on upload question
0
I am working on a portal where teachers upload question on the database. I want to calculate a report which teacher how many questions upload on the current day and show monthly report.
question table
I want Result
I tried code
SELECT * FROM questions WHERE created_at > DATE_SUB(NOW(), INTERVAL 1 DAY) ORDER BY id DESC
mysql database laravel
asked Nov 22 '18 at 7:05
brijbrij
104110
add a comment |
0
I am working on a portal where teachers upload question on the database. I want to calculate a report which teacher how many questions upload on the current day and show monthly report.
question table
I want Result
I tried code
SELECT * FROM questions WHERE created_at > DATE_SUB(NOW(), INTERVAL 1 DAY) ORDER BY id DESC
mysql database laravel
asked Nov 22 '18 at 7:05
brijbrij
104110
Do you have a calendar/dates table , if not are you open to creating one to assist in filling in dates where no uploads have occurred?
– P.Salmon
Nov 22 '18 at 7:52
add a comment |
0
0
0
I am working on a portal where teachers upload question on the database. I want to calculate a report which teacher how many questions upload on the current day and show monthly report.
question table
I want Result
I tried code
SELECT * FROM questions WHERE created_at > DATE_SUB(NOW(), INTERVAL 1 DAY) ORDER BY id DESC
mysql database laravel
asked Nov 22 '18 at 7:05
brijbrij
104110
I am working on a portal where teachers upload question on the database. I want to calculate a report which teacher how many questions upload on the current day and show monthly report.
question table
I want Result
I tried code
SELECT * FROM questions WHERE created_at > DATE_SUB(NOW(), INTERVAL 1 DAY) ORDER BY id DESC
mysql database laravel
mysql database laravel
asked Nov 22 '18 at 7:05
brijbrij
104110
asked Nov 22 '18 at 7:05
brijbrij
104110
asked Nov 22 '18 at 7:05
brijbrij
104110
asked Nov 22 '18 at 7:05
brijbrij
104110
asked Nov 22 '18 at 7:05
brijbrij
104110
104110
Do you have a calendar/dates table , if not are you open to creating one to assist in filling in dates where no uploads have occurred?
– P.Salmon
Nov 22 '18 at 7:52
add a comment |
Do you have a calendar/dates table , if not are you open to creating one to assist in filling in dates where no uploads have occurred?
– P.Salmon
Nov 22 '18 at 7:52
Do you have a calendar/dates table , if not are you open to creating one to assist in filling in dates where no uploads have occurred?
– P.Salmon
Nov 22 '18 at 7:52
Do you have a calendar/dates table , if not are you open to creating one to assist in filling in dates where no uploads have occurred?
– P.Salmon
Nov 22 '18 at 7:52
add a comment |
1 Answer
1
active
oldest
votes
1
You need to use 2 functions here:
DATE and LAST_DAY
Your SQL will look like this
SELECT count(id) AS question_count, DATE(created_at) AS date
FROM questions
WHERE created_at > LAST_DAY(NOW() - INTERVAL 1 MONTH) AND created_at < LAST_DAY(NOW()) + INTERVAL 1 DAY AND user_id = 45
GROUP BY DATE(created_at)
ORDER BY id ASC
Note in the second condition the INTERVAL 1 DAY is outside the LAST_DAY function
answered Nov 22 '18 at 7:46
HossamHossam
936
Thanks, its work, but I want null or o(zero) question_cout column if the teacher did not upload on that date.
– brij
Nov 22 '18 at 9:15
This will complicate the query. I noticed that you tagged Laravel, you can do this part in the Laravel using a simple loop after getting the result from the database.
– Hossam
Nov 22 '18 at 14:43
add a comment |
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
});
}
});
draft saved
draft discarded
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%2f53425516%2ffind-teacher-monthly-report-based-on-upload-question%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
You need to use 2 functions here:
DATE and LAST_DAY
Your SQL will look like this
SELECT count(id) AS question_count, DATE(created_at) AS date
FROM questions
WHERE created_at > LAST_DAY(NOW() - INTERVAL 1 MONTH) AND created_at < LAST_DAY(NOW()) + INTERVAL 1 DAY AND user_id = 45
GROUP BY DATE(created_at)
ORDER BY id ASC
Note in the second condition the INTERVAL 1 DAY is outside the LAST_DAY function
answered Nov 22 '18 at 7:46
HossamHossam
936
Thanks, its work, but I want null or o(zero) question_cout column if the teacher did not upload on that date.
– brij
Nov 22 '18 at 9:15
This will complicate the query. I noticed that you tagged Laravel, you can do this part in the Laravel using a simple loop after getting the result from the database.
– Hossam
Nov 22 '18 at 14:43
add a comment |
1
You need to use 2 functions here:
DATE and LAST_DAY
Your SQL will look like this
SELECT count(id) AS question_count, DATE(created_at) AS date
FROM questions
WHERE created_at > LAST_DAY(NOW() - INTERVAL 1 MONTH) AND created_at < LAST_DAY(NOW()) + INTERVAL 1 DAY AND user_id = 45
GROUP BY DATE(created_at)
ORDER BY id ASC
Note in the second condition the INTERVAL 1 DAY is outside the LAST_DAY function
answered Nov 22 '18 at 7:46
HossamHossam
936
Thanks, its work, but I want null or o(zero) question_cout column if the teacher did not upload on that date.
– brij
Nov 22 '18 at 9:15
This will complicate the query. I noticed that you tagged Laravel, you can do this part in the Laravel using a simple loop after getting the result from the database.
– Hossam
Nov 22 '18 at 14:43
add a comment |
1
1
1
You need to use 2 functions here:
DATE and LAST_DAY
Your SQL will look like this
SELECT count(id) AS question_count, DATE(created_at) AS date
FROM questions
WHERE created_at > LAST_DAY(NOW() - INTERVAL 1 MONTH) AND created_at < LAST_DAY(NOW()) + INTERVAL 1 DAY AND user_id = 45
GROUP BY DATE(created_at)
ORDER BY id ASC
Note in the second condition the INTERVAL 1 DAY is outside the LAST_DAY function
answered Nov 22 '18 at 7:46
HossamHossam
936
You need to use 2 functions here:
DATE and LAST_DAY
Your SQL will look like this
SELECT count(id) AS question_count, DATE(created_at) AS date
FROM questions
WHERE created_at > LAST_DAY(NOW() - INTERVAL 1 MONTH) AND created_at < LAST_DAY(NOW()) + INTERVAL 1 DAY AND user_id = 45
GROUP BY DATE(created_at)
ORDER BY id ASC
Note in the second condition the INTERVAL 1 DAY is outside the LAST_DAY function
answered Nov 22 '18 at 7:46
HossamHossam
936
answered Nov 22 '18 at 7:46
HossamHossam
936
answered Nov 22 '18 at 7:46
HossamHossam
936
answered Nov 22 '18 at 7:46
HossamHossam
936
936
Thanks, its work, but I want null or o(zero) question_cout column if the teacher did not upload on that date.
– brij
Nov 22 '18 at 9:15
This will complicate the query. I noticed that you tagged Laravel, you can do this part in the Laravel using a simple loop after getting the result from the database.
– Hossam
Nov 22 '18 at 14:43
add a comment |
Thanks, its work, but I want null or o(zero) question_cout column if the teacher did not upload on that date.
– brij
Nov 22 '18 at 9:15
This will complicate the query. I noticed that you tagged Laravel, you can do this part in the Laravel using a simple loop after getting the result from the database.
– Hossam
Nov 22 '18 at 14:43
Thanks, its work, but I want null or o(zero) question_cout column if the teacher did not upload on that date.
– brij
Nov 22 '18 at 9:15
Thanks, its work, but I want null or o(zero) question_cout column if the teacher did not upload on that date.
– brij
Nov 22 '18 at 9:15
This will complicate the query. I noticed that you tagged Laravel, you can do this part in the Laravel using a simple loop after getting the result from the database.
– Hossam
Nov 22 '18 at 14:43
This will complicate the query. I noticed that you tagged Laravel, you can do this part in the Laravel using a simple loop after getting the result from the database.
– Hossam
Nov 22 '18 at 14:43
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53425516%2ffind-teacher-monthly-report-based-on-upload-question%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
Do you have a calendar/dates table , if not are you open to creating one to assist in filling in dates where no uploads have occurred?
– P.Salmon
Nov 22 '18 at 7:52