Getting Student's Code who failed more than 45% of their tests
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
1
PostgreSQL
So, I want to return all the student's unique code who have failed more than 45% of their tests that they've taken.
sql postgresql
asked Oct 30 '18 at 2:55
thefalsehumanthefalsehuman
405
add a comment |
1
PostgreSQL
So, I want to return all the student's unique code who have failed more than 45% of their tests that they've taken.
sql postgresql
asked Oct 30 '18 at 2:55
thefalsehumanthefalsehuman
405
This question is poorly phrased. Could you edit it to say what you want to do, rather than your application?
– vy32
Oct 30 '18 at 3:23
You are cross join a1,a2. FixFrom ( ... ) a1 LEFT JOIN (...) a2 ON a1.code=a2.code
– cske
Oct 30 '18 at 4:42
add a comment |
1
1
1
PostgreSQL
So, I want to return all the student's unique code who have failed more than 45% of their tests that they've taken.
sql postgresql
asked Oct 30 '18 at 2:55
thefalsehumanthefalsehuman
405
PostgreSQL
So, I want to return all the student's unique code who have failed more than 45% of their tests that they've taken.
sql postgresql
sql postgresql
asked Oct 30 '18 at 2:55
thefalsehumanthefalsehuman
405
asked Oct 30 '18 at 2:55
thefalsehumanthefalsehuman
405
edited Nov 23 '18 at 15:05
thefalsehuman
asked Oct 30 '18 at 2:55
thefalsehumanthefalsehuman
405
asked Oct 30 '18 at 2:55
thefalsehumanthefalsehuman
405
asked Oct 30 '18 at 2:55
thefalsehumanthefalsehuman
405
405
This question is poorly phrased. Could you edit it to say what you want to do, rather than your application?
– vy32
Oct 30 '18 at 3:23
You are cross join a1,a2. FixFrom ( ... ) a1 LEFT JOIN (...) a2 ON a1.code=a2.code
– cske
Oct 30 '18 at 4:42
add a comment |
This question is poorly phrased. Could you edit it to say what you want to do, rather than your application?
– vy32
Oct 30 '18 at 3:23
You are cross join a1,a2. FixFrom ( ... ) a1 LEFT JOIN (...) a2 ON a1.code=a2.code
– cske
Oct 30 '18 at 4:42
This question is poorly phrased. Could you edit it to say what you want to do, rather than your application?
– vy32
Oct 30 '18 at 3:23
This question is poorly phrased. Could you edit it to say what you want to do, rather than your application?
– vy32
Oct 30 '18 at 3:23
You are cross join a1,a2. Fix
From ( ... ) a1 LEFT JOIN (...) a2 ON a1.code=a2.code– cske
Oct 30 '18 at 4:42
You are cross join a1,a2. Fix
From ( ... ) a1 LEFT JOIN (...) a2 ON a1.code=a2.code– cske
Oct 30 '18 at 4:42
add a comment |
2 Answers
2
active
oldest
votes
0
You could use this:
Assume as in your data - Grade (75 or above is passing) => grade < 75 is failed
SELECT code
FROM exams
GROUP BY code
HAVING SUM(CASE WHEN grade < 75 THEN 1 ELSE 0 END) :: FLOAT / COUNT(grade) > 0.45;
This is Sqlfiddle
answered Oct 30 '18 at 3:10
Pham X. BachPham X. Bach
3,92821629
SELECT code FROM students, exams GROUP BY code HAVING SUM(CASE WHEN exams.grade < 75 THEN 1 ELSE 0 END) / COUNT(exams.grade) > 0.3; I edited it because of errors with From and grade, but I get no rows from this
– thefalsehuman
Oct 30 '18 at 3:15
Hmmm still no rows being outputted. I think the recommended way to do this is to calculate the total tests and the total failed tests per student and compare those two columns, but I'm not sure what's wrong with my code. Thanks!
– thefalsehuman
Oct 30 '18 at 3:32
@thefalsehuman edited my answer. That is exactly what it did, calculate the number of failed exams, then divine by total exams. Must convert to float first or the divine will be FLOORed to nearest integer (0)
– Pham X. Bach
Oct 30 '18 at 4:04
Yup, it works now, thanks!
– thefalsehuman
Oct 30 '18 at 4:58
add a comment |
0
I would simply write this as:
SELECT e.code
FROM exams e
GROUP BY e.code
HAVING AVG( (e.grade < 75)::numeric ) > 0.45;
answered Oct 30 '18 at 11:35
Gordon LinoffGordon Linoff
794k37318423
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%2f53056733%2fgetting-students-code-who-failed-more-than-45-of-their-tests%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
You could use this:
Assume as in your data - Grade (75 or above is passing) => grade < 75 is failed
SELECT code
FROM exams
GROUP BY code
HAVING SUM(CASE WHEN grade < 75 THEN 1 ELSE 0 END) :: FLOAT / COUNT(grade) > 0.45;
This is Sqlfiddle
answered Oct 30 '18 at 3:10
Pham X. BachPham X. Bach
3,92821629
SELECT code FROM students, exams GROUP BY code HAVING SUM(CASE WHEN exams.grade < 75 THEN 1 ELSE 0 END) / COUNT(exams.grade) > 0.3; I edited it because of errors with From and grade, but I get no rows from this
– thefalsehuman
Oct 30 '18 at 3:15
Hmmm still no rows being outputted. I think the recommended way to do this is to calculate the total tests and the total failed tests per student and compare those two columns, but I'm not sure what's wrong with my code. Thanks!
– thefalsehuman
Oct 30 '18 at 3:32
@thefalsehuman edited my answer. That is exactly what it did, calculate the number of failed exams, then divine by total exams. Must convert to float first or the divine will be FLOORed to nearest integer (0)
– Pham X. Bach
Oct 30 '18 at 4:04
Yup, it works now, thanks!
– thefalsehuman
Oct 30 '18 at 4:58
add a comment |
0
You could use this:
Assume as in your data - Grade (75 or above is passing) => grade < 75 is failed
SELECT code
FROM exams
GROUP BY code
HAVING SUM(CASE WHEN grade < 75 THEN 1 ELSE 0 END) :: FLOAT / COUNT(grade) > 0.45;
This is Sqlfiddle
answered Oct 30 '18 at 3:10
Pham X. BachPham X. Bach
3,92821629
SELECT code FROM students, exams GROUP BY code HAVING SUM(CASE WHEN exams.grade < 75 THEN 1 ELSE 0 END) / COUNT(exams.grade) > 0.3; I edited it because of errors with From and grade, but I get no rows from this
– thefalsehuman
Oct 30 '18 at 3:15
Hmmm still no rows being outputted. I think the recommended way to do this is to calculate the total tests and the total failed tests per student and compare those two columns, but I'm not sure what's wrong with my code. Thanks!
– thefalsehuman
Oct 30 '18 at 3:32
@thefalsehuman edited my answer. That is exactly what it did, calculate the number of failed exams, then divine by total exams. Must convert to float first or the divine will be FLOORed to nearest integer (0)
– Pham X. Bach
Oct 30 '18 at 4:04
Yup, it works now, thanks!
– thefalsehuman
Oct 30 '18 at 4:58
add a comment |
0
0
0
You could use this:
Assume as in your data - Grade (75 or above is passing) => grade < 75 is failed
SELECT code
FROM exams
GROUP BY code
HAVING SUM(CASE WHEN grade < 75 THEN 1 ELSE 0 END) :: FLOAT / COUNT(grade) > 0.45;
This is Sqlfiddle
answered Oct 30 '18 at 3:10
Pham X. BachPham X. Bach
3,92821629
You could use this:
Assume as in your data - Grade (75 or above is passing) => grade < 75 is failed
SELECT code
FROM exams
GROUP BY code
HAVING SUM(CASE WHEN grade < 75 THEN 1 ELSE 0 END) :: FLOAT / COUNT(grade) > 0.45;
This is Sqlfiddle
answered Oct 30 '18 at 3:10
Pham X. BachPham X. Bach
3,92821629
edited Oct 30 '18 at 4:01
answered Oct 30 '18 at 3:10
Pham X. BachPham X. Bach
3,92821629
answered Oct 30 '18 at 3:10
Pham X. BachPham X. Bach
3,92821629
answered Oct 30 '18 at 3:10
Pham X. BachPham X. Bach
3,92821629
3,92821629
SELECT code FROM students, exams GROUP BY code HAVING SUM(CASE WHEN exams.grade < 75 THEN 1 ELSE 0 END) / COUNT(exams.grade) > 0.3; I edited it because of errors with From and grade, but I get no rows from this
– thefalsehuman
Oct 30 '18 at 3:15
Hmmm still no rows being outputted. I think the recommended way to do this is to calculate the total tests and the total failed tests per student and compare those two columns, but I'm not sure what's wrong with my code. Thanks!
– thefalsehuman
Oct 30 '18 at 3:32
@thefalsehuman edited my answer. That is exactly what it did, calculate the number of failed exams, then divine by total exams. Must convert to float first or the divine will be FLOORed to nearest integer (0)
– Pham X. Bach
Oct 30 '18 at 4:04
Yup, it works now, thanks!
– thefalsehuman
Oct 30 '18 at 4:58
add a comment |
SELECT code FROM students, exams GROUP BY code HAVING SUM(CASE WHEN exams.grade < 75 THEN 1 ELSE 0 END) / COUNT(exams.grade) > 0.3; I edited it because of errors with From and grade, but I get no rows from this
– thefalsehuman
Oct 30 '18 at 3:15
Hmmm still no rows being outputted. I think the recommended way to do this is to calculate the total tests and the total failed tests per student and compare those two columns, but I'm not sure what's wrong with my code. Thanks!
– thefalsehuman
Oct 30 '18 at 3:32
@thefalsehuman edited my answer. That is exactly what it did, calculate the number of failed exams, then divine by total exams. Must convert to float first or the divine will be FLOORed to nearest integer (0)
– Pham X. Bach
Oct 30 '18 at 4:04
Yup, it works now, thanks!
– thefalsehuman
Oct 30 '18 at 4:58
SELECT code FROM students, exams GROUP BY code HAVING SUM(CASE WHEN exams.grade < 75 THEN 1 ELSE 0 END) / COUNT(exams.grade) > 0.3; I edited it because of errors with From and grade, but I get no rows from this
– thefalsehuman
Oct 30 '18 at 3:15
SELECT code FROM students, exams GROUP BY code HAVING SUM(CASE WHEN exams.grade < 75 THEN 1 ELSE 0 END) / COUNT(exams.grade) > 0.3; I edited it because of errors with From and grade, but I get no rows from this
– thefalsehuman
Oct 30 '18 at 3:15
Hmmm still no rows being outputted. I think the recommended way to do this is to calculate the total tests and the total failed tests per student and compare those two columns, but I'm not sure what's wrong with my code. Thanks!
– thefalsehuman
Oct 30 '18 at 3:32
Hmmm still no rows being outputted. I think the recommended way to do this is to calculate the total tests and the total failed tests per student and compare those two columns, but I'm not sure what's wrong with my code. Thanks!
– thefalsehuman
Oct 30 '18 at 3:32
@thefalsehuman edited my answer. That is exactly what it did, calculate the number of failed exams, then divine by total exams. Must convert to float first or the divine will be FLOORed to nearest integer (0)
– Pham X. Bach
Oct 30 '18 at 4:04
@thefalsehuman edited my answer. That is exactly what it did, calculate the number of failed exams, then divine by total exams. Must convert to float first or the divine will be FLOORed to nearest integer (0)
– Pham X. Bach
Oct 30 '18 at 4:04
Yup, it works now, thanks!
– thefalsehuman
Oct 30 '18 at 4:58
Yup, it works now, thanks!
– thefalsehuman
Oct 30 '18 at 4:58
add a comment |
0
I would simply write this as:
SELECT e.code
FROM exams e
GROUP BY e.code
HAVING AVG( (e.grade < 75)::numeric ) > 0.45;
answered Oct 30 '18 at 11:35
Gordon LinoffGordon Linoff
794k37318423
add a comment |
0
I would simply write this as:
SELECT e.code
FROM exams e
GROUP BY e.code
HAVING AVG( (e.grade < 75)::numeric ) > 0.45;
answered Oct 30 '18 at 11:35
Gordon LinoffGordon Linoff
794k37318423
add a comment |
0
0
0
I would simply write this as:
SELECT e.code
FROM exams e
GROUP BY e.code
HAVING AVG( (e.grade < 75)::numeric ) > 0.45;
answered Oct 30 '18 at 11:35
Gordon LinoffGordon Linoff
794k37318423
I would simply write this as:
SELECT e.code
FROM exams e
GROUP BY e.code
HAVING AVG( (e.grade < 75)::numeric ) > 0.45;
answered Oct 30 '18 at 11:35
Gordon LinoffGordon Linoff
794k37318423
answered Oct 30 '18 at 11:35
Gordon LinoffGordon Linoff
794k37318423
answered Oct 30 '18 at 11:35
Gordon LinoffGordon Linoff
794k37318423
answered Oct 30 '18 at 11:35
Gordon LinoffGordon Linoff
794k37318423
794k37318423
add a comment |
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%2f53056733%2fgetting-students-code-who-failed-more-than-45-of-their-tests%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
This question is poorly phrased. Could you edit it to say what you want to do, rather than your application?
– vy32
Oct 30 '18 at 3:23
You are cross join a1,a2. Fix
From ( ... ) a1 LEFT JOIN (...) a2 ON a1.code=a2.code– cske
Oct 30 '18 at 4:42