Mixing
Can't Understand the outcome
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I can't understand two different results.
I am trying to get the top 10% of students' GPA, sorted by their grade point average.
select top 10 percent avg (markrate) as GPA
from mark
Output:
Now when I query
select top 10 percent avg (markrate) as gpa, studentid
from mark
group by studentid
I am assuming it's because there is the bunch of StudentID 1's and 2's so the average GPA changed
Now
select top 10 percent avg (markrate) as gpa, studentid
from mark
group by studentid
order by gpa
How come it ended getting 82 and 2? and Why is this the correct answer?
sql
sql-server edited Jan 3 at 5:06
Prashant Pimpale
3,80541035
asked Jan 3 at 5:04
slayersslayers
11
add a comment |
I can't understand two different results.
I am trying to get the top 10% of students' GPA, sorted by their grade point average.
select top 10 percent avg (markrate) as GPA
from mark
Output:
Now when I query
select top 10 percent avg (markrate) as gpa, studentid
from mark
group by studentid
I am assuming it's because there is the bunch of StudentID 1's and 2's so the average GPA changed
Now
select top 10 percent avg (markrate) as gpa, studentid
from mark
group by studentid
order by gpa
How come it ended getting 82 and 2? and Why is this the correct answer?
sql
sql-server edited Jan 3 at 5:06
Prashant Pimpale
3,80541035
asked Jan 3 at 5:04
slayersslayers
11
2
Request you to put sample data and expected output in text format rather than in image.
– Suraj Kumar
Jan 3 at 5:08
add a comment |
I can't understand two different results.
I am trying to get the top 10% of students' GPA, sorted by their grade point average.
select top 10 percent avg (markrate) as GPA
from mark
Output:
Now when I query
select top 10 percent avg (markrate) as gpa, studentid
from mark
group by studentid
I am assuming it's because there is the bunch of StudentID 1's and 2's so the average GPA changed
Now
select top 10 percent avg (markrate) as gpa, studentid
from mark
group by studentid
order by gpa
How come it ended getting 82 and 2? and Why is this the correct answer?
sql
sql-server edited Jan 3 at 5:06
Prashant Pimpale
3,80541035
asked Jan 3 at 5:04
slayersslayers
11
I can't understand two different results.
I am trying to get the top 10% of students' GPA, sorted by their grade point average.
select top 10 percent avg (markrate) as GPA
from mark
Output:
Now when I query
select top 10 percent avg (markrate) as gpa, studentid
from mark
group by studentid
I am assuming it's because there is the bunch of StudentID 1's and 2's so the average GPA changed
Now
select top 10 percent avg (markrate) as gpa, studentid
from mark
group by studentid
order by gpa
How come it ended getting 82 and 2? and Why is this the correct answer?
sql
sql-server sql
sql-server edited Jan 3 at 5:06
Prashant Pimpale
3,80541035
asked Jan 3 at 5:04
slayersslayers
11
edited Jan 3 at 5:06
Prashant Pimpale
3,80541035
asked Jan 3 at 5:04
slayersslayers
11
edited Jan 3 at 5:06
Prashant Pimpale
3,80541035
edited Jan 3 at 5:06
Prashant Pimpale
3,80541035
edited Jan 3 at 5:06
Prashant Pimpale
3,80541035
3,80541035
asked Jan 3 at 5:04
slayersslayers
11
asked Jan 3 at 5:04
slayersslayers
11
asked Jan 3 at 5:04
slayersslayers
11
11
2
Request you to put sample data and expected output in text format rather than in image.
– Suraj Kumar
Jan 3 at 5:08
add a comment |
2
Request you to put sample data and expected output in text format rather than in image.
– Suraj Kumar
Jan 3 at 5:08
2
2
Request you to put sample data and expected output in text format rather than in image.
– Suraj Kumar
Jan 3 at 5:08
Request you to put sample data and expected output in text format rather than in image.
– Suraj Kumar
Jan 3 at 5:08
add a comment |
3 Answers
3
active
oldest
votes
This query:
select top 10 percent avg(markrate) as gpa, studentid
from mark
group by studentid
Is probably not doing what you expect. It is aggregating all the data by studentid. Then it takes the "top 10 percent" of the result rows. SQL Server is rounding this to 1 row.
Which row? An arbitrary row. Using top without order by is highly discouraged, because you get indeterminate rows. If you run the query without the top (or with top 100 percent), you'll see the averages for each student. One of those rows is arbitrarily chosen.
When you add the order by gpa, you get the student with the lowest GPA. The average is 82 rather than 82.5 because the column is an integer -- so the result is an integer.
I would be more inclined to look at the results using decimal numbers -- and to consider ties:
select top 10 percent with ties avg(markrate * 1.0) as gpa, studentid
from mark
group by studentid
order by gpa
answered Jan 3 at 12:45
Gordon LinoffGordon Linoff
794k37318421
add a comment |
Because the studentID 2 is the one with lower gpa, as per my calculations it is 82.5 GPA but I guess the datatype for MarkRate is integer, so, it rounds it to 82
answered Jan 3 at 5:15
Angel M.Angel M.
1,312415
add a comment |
Try out your queries without top 10 percent, Hopefully it will clear your doubts.
1. As in your first query without using top 10 percent
select avg (markrate) as GPA
from mark
This will return only one record which will be the avg value of markrate. If you use top 10 percent then it will return 10% rows from the complete result.
2. In your second query without using top 10 percent
select avg (markrate) as gpa, studentid
from mark
group by studentid
3. This will return four records which will be the avg value of markrate per studentid. If you use top 10 percent then it will return 10% rows from these 4 rows result.
In your third query same thing will happen as second but result is sorted by gpa, So it shows different result than second one.
answered Jan 3 at 5:17
KaranKaran
3,4262525
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
});
}
});
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%2f54016618%2fcant-understand-the-outcome%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
This query:
select top 10 percent avg(markrate) as gpa, studentid
from mark
group by studentid
Is probably not doing what you expect. It is aggregating all the data by studentid. Then it takes the "top 10 percent" of the result rows. SQL Server is rounding this to 1 row.
Which row? An arbitrary row. Using top without order by is highly discouraged, because you get indeterminate rows. If you run the query without the top (or with top 100 percent), you'll see the averages for each student. One of those rows is arbitrarily chosen.
When you add the order by gpa, you get the student with the lowest GPA. The average is 82 rather than 82.5 because the column is an integer -- so the result is an integer.
I would be more inclined to look at the results using decimal numbers -- and to consider ties:
select top 10 percent with ties avg(markrate * 1.0) as gpa, studentid
from mark
group by studentid
order by gpa
answered Jan 3 at 12:45
Gordon LinoffGordon Linoff
794k37318421
add a comment |
This query:
select top 10 percent avg(markrate) as gpa, studentid
from mark
group by studentid
Is probably not doing what you expect. It is aggregating all the data by studentid. Then it takes the "top 10 percent" of the result rows. SQL Server is rounding this to 1 row.
Which row? An arbitrary row. Using top without order by is highly discouraged, because you get indeterminate rows. If you run the query without the top (or with top 100 percent), you'll see the averages for each student. One of those rows is arbitrarily chosen.
When you add the order by gpa, you get the student with the lowest GPA. The average is 82 rather than 82.5 because the column is an integer -- so the result is an integer.
I would be more inclined to look at the results using decimal numbers -- and to consider ties:
select top 10 percent with ties avg(markrate * 1.0) as gpa, studentid
from mark
group by studentid
order by gpa
answered Jan 3 at 12:45
Gordon LinoffGordon Linoff
794k37318421
add a comment |
This query:
select top 10 percent avg(markrate) as gpa, studentid
from mark
group by studentid
Is probably not doing what you expect. It is aggregating all the data by studentid. Then it takes the "top 10 percent" of the result rows. SQL Server is rounding this to 1 row.
Which row? An arbitrary row. Using top without order by is highly discouraged, because you get indeterminate rows. If you run the query without the top (or with top 100 percent), you'll see the averages for each student. One of those rows is arbitrarily chosen.
When you add the order by gpa, you get the student with the lowest GPA. The average is 82 rather than 82.5 because the column is an integer -- so the result is an integer.
I would be more inclined to look at the results using decimal numbers -- and to consider ties:
select top 10 percent with ties avg(markrate * 1.0) as gpa, studentid
from mark
group by studentid
order by gpa
answered Jan 3 at 12:45
Gordon LinoffGordon Linoff
794k37318421
This query:
select top 10 percent avg(markrate) as gpa, studentid
from mark
group by studentid
Is probably not doing what you expect. It is aggregating all the data by studentid. Then it takes the "top 10 percent" of the result rows. SQL Server is rounding this to 1 row.
Which row? An arbitrary row. Using top without order by is highly discouraged, because you get indeterminate rows. If you run the query without the top (or with top 100 percent), you'll see the averages for each student. One of those rows is arbitrarily chosen.
When you add the order by gpa, you get the student with the lowest GPA. The average is 82 rather than 82.5 because the column is an integer -- so the result is an integer.
I would be more inclined to look at the results using decimal numbers -- and to consider ties:
select top 10 percent with ties avg(markrate * 1.0) as gpa, studentid
from mark
group by studentid
order by gpa
answered Jan 3 at 12:45
Gordon LinoffGordon Linoff
794k37318421
answered Jan 3 at 12:45
Gordon LinoffGordon Linoff
794k37318421
answered Jan 3 at 12:45
Gordon LinoffGordon Linoff
794k37318421
answered Jan 3 at 12:45
Gordon LinoffGordon Linoff
794k37318421
794k37318421
add a comment |
add a comment |
Because the studentID 2 is the one with lower gpa, as per my calculations it is 82.5 GPA but I guess the datatype for MarkRate is integer, so, it rounds it to 82
answered Jan 3 at 5:15
Angel M.Angel M.
1,312415
add a comment |
Because the studentID 2 is the one with lower gpa, as per my calculations it is 82.5 GPA but I guess the datatype for MarkRate is integer, so, it rounds it to 82
answered Jan 3 at 5:15
Angel M.Angel M.
1,312415
add a comment |
Because the studentID 2 is the one with lower gpa, as per my calculations it is 82.5 GPA but I guess the datatype for MarkRate is integer, so, it rounds it to 82
answered Jan 3 at 5:15
Angel M.Angel M.
1,312415
Because the studentID 2 is the one with lower gpa, as per my calculations it is 82.5 GPA but I guess the datatype for MarkRate is integer, so, it rounds it to 82
answered Jan 3 at 5:15
Angel M.Angel M.
1,312415
answered Jan 3 at 5:15
Angel M.Angel M.
1,312415
answered Jan 3 at 5:15
Angel M.Angel M.
1,312415
answered Jan 3 at 5:15
Angel M.Angel M.
1,312415
1,312415
add a comment |
add a comment |
Try out your queries without top 10 percent, Hopefully it will clear your doubts.
1. As in your first query without using top 10 percent
select avg (markrate) as GPA
from mark
This will return only one record which will be the avg value of markrate. If you use top 10 percent then it will return 10% rows from the complete result.
2. In your second query without using top 10 percent
select avg (markrate) as gpa, studentid
from mark
group by studentid
3. This will return four records which will be the avg value of markrate per studentid. If you use top 10 percent then it will return 10% rows from these 4 rows result.
In your third query same thing will happen as second but result is sorted by gpa, So it shows different result than second one.
answered Jan 3 at 5:17
KaranKaran
3,4262525
add a comment |
Try out your queries without top 10 percent, Hopefully it will clear your doubts.
1. As in your first query without using top 10 percent
select avg (markrate) as GPA
from mark
This will return only one record which will be the avg value of markrate. If you use top 10 percent then it will return 10% rows from the complete result.
2. In your second query without using top 10 percent
select avg (markrate) as gpa, studentid
from mark
group by studentid
3. This will return four records which will be the avg value of markrate per studentid. If you use top 10 percent then it will return 10% rows from these 4 rows result.
In your third query same thing will happen as second but result is sorted by gpa, So it shows different result than second one.
answered Jan 3 at 5:17
KaranKaran
3,4262525
add a comment |
Try out your queries without top 10 percent, Hopefully it will clear your doubts.
1. As in your first query without using top 10 percent
select avg (markrate) as GPA
from mark
This will return only one record which will be the avg value of markrate. If you use top 10 percent then it will return 10% rows from the complete result.
2. In your second query without using top 10 percent
select avg (markrate) as gpa, studentid
from mark
group by studentid
3. This will return four records which will be the avg value of markrate per studentid. If you use top 10 percent then it will return 10% rows from these 4 rows result.
In your third query same thing will happen as second but result is sorted by gpa, So it shows different result than second one.
answered Jan 3 at 5:17
KaranKaran
3,4262525
Try out your queries without top 10 percent, Hopefully it will clear your doubts.
1. As in your first query without using top 10 percent
select avg (markrate) as GPA
from mark
This will return only one record which will be the avg value of markrate. If you use top 10 percent then it will return 10% rows from the complete result.
2. In your second query without using top 10 percent
select avg (markrate) as gpa, studentid
from mark
group by studentid
3. This will return four records which will be the avg value of markrate per studentid. If you use top 10 percent then it will return 10% rows from these 4 rows result.
In your third query same thing will happen as second but result is sorted by gpa, So it shows different result than second one.
answered Jan 3 at 5:17
KaranKaran
3,4262525
answered Jan 3 at 5:17
KaranKaran
3,4262525
answered Jan 3 at 5:17
KaranKaran
3,4262525
answered Jan 3 at 5:17
KaranKaran
3,4262525
3,4262525
add a comment |
add a comment |
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%2f54016618%2fcant-understand-the-outcome%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
2
Request you to put sample data and expected output in text format rather than in image.
– Suraj Kumar
Jan 3 at 5:08