Calculate separate percentage value for multiple ID's in SQL
I have a table with few columns CitrixID, Wave, DocNumber, Incorrect, correct and Percentage
How to get separate percentage values for multiple CitrixID's?
I have written the below query in sql but it returns wrong percentage value.
SELECT Citrix_ID, Wave,Document_Number, SUM(Attempt) as Incorrect,SUM(AttemptGreen) as Correct,
CONCAT((SUM(AttemptGreen) *100 / (SELECT SUM(AttemptGreen) + SUM(Attempt) FROM tblTrackQuestErrors WHERE Wave='Wave 86' and Document_Number='0123123123116')),'%') AS Percentage
FROM tblTrackQuestErrors WHERE Wave='Wave 86' and Document_Number='0123123123116' group by Citrix_ID,Wave,Document_Number
I need the output to be like below
sql-server edited Jan 19 at 17:07
James Z
11.2k71935
asked Jan 19 at 11:17
Sudhakar ChitlamSudhakar Chitlam
72
add a comment |
I have a table with few columns CitrixID, Wave, DocNumber, Incorrect, correct and Percentage
How to get separate percentage values for multiple CitrixID's?
I have written the below query in sql but it returns wrong percentage value.
SELECT Citrix_ID, Wave,Document_Number, SUM(Attempt) as Incorrect,SUM(AttemptGreen) as Correct,
CONCAT((SUM(AttemptGreen) *100 / (SELECT SUM(AttemptGreen) + SUM(Attempt) FROM tblTrackQuestErrors WHERE Wave='Wave 86' and Document_Number='0123123123116')),'%') AS Percentage
FROM tblTrackQuestErrors WHERE Wave='Wave 86' and Document_Number='0123123123116' group by Citrix_ID,Wave,Document_Number
I need the output to be like below
sql-server edited Jan 19 at 17:07
James Z
11.2k71935
asked Jan 19 at 11:17
Sudhakar ChitlamSudhakar Chitlam
72
add a comment |
I have a table with few columns CitrixID, Wave, DocNumber, Incorrect, correct and Percentage
How to get separate percentage values for multiple CitrixID's?
I have written the below query in sql but it returns wrong percentage value.
SELECT Citrix_ID, Wave,Document_Number, SUM(Attempt) as Incorrect,SUM(AttemptGreen) as Correct,
CONCAT((SUM(AttemptGreen) *100 / (SELECT SUM(AttemptGreen) + SUM(Attempt) FROM tblTrackQuestErrors WHERE Wave='Wave 86' and Document_Number='0123123123116')),'%') AS Percentage
FROM tblTrackQuestErrors WHERE Wave='Wave 86' and Document_Number='0123123123116' group by Citrix_ID,Wave,Document_Number
I need the output to be like below
sql-server edited Jan 19 at 17:07
James Z
11.2k71935
asked Jan 19 at 11:17
Sudhakar ChitlamSudhakar Chitlam
72
I have a table with few columns CitrixID, Wave, DocNumber, Incorrect, correct and Percentage
How to get separate percentage values for multiple CitrixID's?
I have written the below query in sql but it returns wrong percentage value.
SELECT Citrix_ID, Wave,Document_Number, SUM(Attempt) as Incorrect,SUM(AttemptGreen) as Correct,
CONCAT((SUM(AttemptGreen) *100 / (SELECT SUM(AttemptGreen) + SUM(Attempt) FROM tblTrackQuestErrors WHERE Wave='Wave 86' and Document_Number='0123123123116')),'%') AS Percentage
FROM tblTrackQuestErrors WHERE Wave='Wave 86' and Document_Number='0123123123116' group by Citrix_ID,Wave,Document_Number
I need the output to be like below
sql-server sql-server edited Jan 19 at 17:07
James Z
11.2k71935
asked Jan 19 at 11:17
Sudhakar ChitlamSudhakar Chitlam
72
edited Jan 19 at 17:07
James Z
11.2k71935
asked Jan 19 at 11:17
Sudhakar ChitlamSudhakar Chitlam
72
edited Jan 19 at 17:07
James Z
11.2k71935
edited Jan 19 at 17:07
James Z
11.2k71935
edited Jan 19 at 17:07
James Z
11.2k71935
11.2k71935
asked Jan 19 at 11:17
Sudhakar ChitlamSudhakar Chitlam
72
asked Jan 19 at 11:17
Sudhakar ChitlamSudhakar Chitlam
72
asked Jan 19 at 11:17
Sudhakar ChitlamSudhakar Chitlam
72
72
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
Sudhakar, could you please refer to following SELECT and percentage calculation
select
convert(
decimal(5,2),
isnull(correct,0) * 100.0 / case when (isnull(correct,0) + isnull(incorrect,0)) = 0
then null
else (isnull(correct,0) + isnull(incorrect,0))
end
) as [percentage]
from tbl
answered Jan 19 at 12:38
EralperEralper
5,21011221
Awsome Eralper. Its worked like a charm. Thanks for saving time.
– Sudhakar Chitlam
Jan 21 at 6:38
add a comment |
I don't have your tables, but this should work
SELECT Citrix_ID, Wave,Document_Number, SUM(Attempt) as Incorrect, SUM(AttemptGreen) as Correct
, SUM(AttemptGreen) *100 / SUM(AttemptGreen) + SUM(Attempt) AS Percentage
,MIN((
SELECT SUM(T1.AttemptGreen) *100 / SUM(T1.AttemptGreen) + SUM(T1.Attempt)
FROM tblTrackQuestErrors T1
WHERE T1.Wave = tblTrackQuestErrors.Wave
AND T1.Document_Number = tblTrackQuestErrors.Document_Number
)) AS Percentage_Multi_CitrixID
FROM tblTrackQuestErrors
group by Citrix_ID,Wave,Document_Number
answered Jan 20 at 12:16
Gert-JanGert-Jan
444
add a comment |
SELECT Citrix_ID, Wave,Document_Number, SUM(Attempt) as Incorrect,SUM(AttemptGreen) as Correct,
CONCAT(CONVERT(DECIMAL(5,2), SUM(AttemptGreen) * 100.0 / CASE WHEN (SUM(AttemptGreen) + SUM(Attempt)) = 0 THEN NULL ELSE (SUM(AttemptGreen) + SUM(Attempt)) END ),'%') as [percentage]
FROM tblTrackQuestErrors WHERE Wave=@Wave and Document_Number=@Document_Number group by Citrix_ID,Wave,Document_Number
answered Jan 21 at 6:38
Sudhakar ChitlamSudhakar Chitlam
72
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%2f54266512%2fcalculate-separate-percentage-value-for-multiple-ids-in-sql%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
Sudhakar, could you please refer to following SELECT and percentage calculation
select
convert(
decimal(5,2),
isnull(correct,0) * 100.0 / case when (isnull(correct,0) + isnull(incorrect,0)) = 0
then null
else (isnull(correct,0) + isnull(incorrect,0))
end
) as [percentage]
from tbl
answered Jan 19 at 12:38
EralperEralper
5,21011221
Awsome Eralper. Its worked like a charm. Thanks for saving time.
– Sudhakar Chitlam
Jan 21 at 6:38
add a comment |
Sudhakar, could you please refer to following SELECT and percentage calculation
select
convert(
decimal(5,2),
isnull(correct,0) * 100.0 / case when (isnull(correct,0) + isnull(incorrect,0)) = 0
then null
else (isnull(correct,0) + isnull(incorrect,0))
end
) as [percentage]
from tbl
answered Jan 19 at 12:38
EralperEralper
5,21011221
Awsome Eralper. Its worked like a charm. Thanks for saving time.
– Sudhakar Chitlam
Jan 21 at 6:38
add a comment |
Sudhakar, could you please refer to following SELECT and percentage calculation
select
convert(
decimal(5,2),
isnull(correct,0) * 100.0 / case when (isnull(correct,0) + isnull(incorrect,0)) = 0
then null
else (isnull(correct,0) + isnull(incorrect,0))
end
) as [percentage]
from tbl
answered Jan 19 at 12:38
EralperEralper
5,21011221
Sudhakar, could you please refer to following SELECT and percentage calculation
select
convert(
decimal(5,2),
isnull(correct,0) * 100.0 / case when (isnull(correct,0) + isnull(incorrect,0)) = 0
then null
else (isnull(correct,0) + isnull(incorrect,0))
end
) as [percentage]
from tbl
answered Jan 19 at 12:38
EralperEralper
5,21011221
answered Jan 19 at 12:38
EralperEralper
5,21011221
answered Jan 19 at 12:38
EralperEralper
5,21011221
answered Jan 19 at 12:38
EralperEralper
5,21011221
5,21011221
Awsome Eralper. Its worked like a charm. Thanks for saving time.
– Sudhakar Chitlam
Jan 21 at 6:38
add a comment |
Awsome Eralper. Its worked like a charm. Thanks for saving time.
– Sudhakar Chitlam
Jan 21 at 6:38
Awsome Eralper. Its worked like a charm. Thanks for saving time.
– Sudhakar Chitlam
Jan 21 at 6:38
Awsome Eralper. Its worked like a charm. Thanks for saving time.
– Sudhakar Chitlam
Jan 21 at 6:38
add a comment |
I don't have your tables, but this should work
SELECT Citrix_ID, Wave,Document_Number, SUM(Attempt) as Incorrect, SUM(AttemptGreen) as Correct
, SUM(AttemptGreen) *100 / SUM(AttemptGreen) + SUM(Attempt) AS Percentage
,MIN((
SELECT SUM(T1.AttemptGreen) *100 / SUM(T1.AttemptGreen) + SUM(T1.Attempt)
FROM tblTrackQuestErrors T1
WHERE T1.Wave = tblTrackQuestErrors.Wave
AND T1.Document_Number = tblTrackQuestErrors.Document_Number
)) AS Percentage_Multi_CitrixID
FROM tblTrackQuestErrors
group by Citrix_ID,Wave,Document_Number
answered Jan 20 at 12:16
Gert-JanGert-Jan
444
add a comment |
I don't have your tables, but this should work
SELECT Citrix_ID, Wave,Document_Number, SUM(Attempt) as Incorrect, SUM(AttemptGreen) as Correct
, SUM(AttemptGreen) *100 / SUM(AttemptGreen) + SUM(Attempt) AS Percentage
,MIN((
SELECT SUM(T1.AttemptGreen) *100 / SUM(T1.AttemptGreen) + SUM(T1.Attempt)
FROM tblTrackQuestErrors T1
WHERE T1.Wave = tblTrackQuestErrors.Wave
AND T1.Document_Number = tblTrackQuestErrors.Document_Number
)) AS Percentage_Multi_CitrixID
FROM tblTrackQuestErrors
group by Citrix_ID,Wave,Document_Number
answered Jan 20 at 12:16
Gert-JanGert-Jan
444
add a comment |
I don't have your tables, but this should work
SELECT Citrix_ID, Wave,Document_Number, SUM(Attempt) as Incorrect, SUM(AttemptGreen) as Correct
, SUM(AttemptGreen) *100 / SUM(AttemptGreen) + SUM(Attempt) AS Percentage
,MIN((
SELECT SUM(T1.AttemptGreen) *100 / SUM(T1.AttemptGreen) + SUM(T1.Attempt)
FROM tblTrackQuestErrors T1
WHERE T1.Wave = tblTrackQuestErrors.Wave
AND T1.Document_Number = tblTrackQuestErrors.Document_Number
)) AS Percentage_Multi_CitrixID
FROM tblTrackQuestErrors
group by Citrix_ID,Wave,Document_Number
answered Jan 20 at 12:16
Gert-JanGert-Jan
444
I don't have your tables, but this should work
SELECT Citrix_ID, Wave,Document_Number, SUM(Attempt) as Incorrect, SUM(AttemptGreen) as Correct
, SUM(AttemptGreen) *100 / SUM(AttemptGreen) + SUM(Attempt) AS Percentage
,MIN((
SELECT SUM(T1.AttemptGreen) *100 / SUM(T1.AttemptGreen) + SUM(T1.Attempt)
FROM tblTrackQuestErrors T1
WHERE T1.Wave = tblTrackQuestErrors.Wave
AND T1.Document_Number = tblTrackQuestErrors.Document_Number
)) AS Percentage_Multi_CitrixID
FROM tblTrackQuestErrors
group by Citrix_ID,Wave,Document_Number
answered Jan 20 at 12:16
Gert-JanGert-Jan
444
answered Jan 20 at 12:16
Gert-JanGert-Jan
444
answered Jan 20 at 12:16
Gert-JanGert-Jan
444
answered Jan 20 at 12:16
Gert-JanGert-Jan
444
444
add a comment |
add a comment |
SELECT Citrix_ID, Wave,Document_Number, SUM(Attempt) as Incorrect,SUM(AttemptGreen) as Correct,
CONCAT(CONVERT(DECIMAL(5,2), SUM(AttemptGreen) * 100.0 / CASE WHEN (SUM(AttemptGreen) + SUM(Attempt)) = 0 THEN NULL ELSE (SUM(AttemptGreen) + SUM(Attempt)) END ),'%') as [percentage]
FROM tblTrackQuestErrors WHERE Wave=@Wave and Document_Number=@Document_Number group by Citrix_ID,Wave,Document_Number
answered Jan 21 at 6:38
Sudhakar ChitlamSudhakar Chitlam
72
add a comment |
SELECT Citrix_ID, Wave,Document_Number, SUM(Attempt) as Incorrect,SUM(AttemptGreen) as Correct,
CONCAT(CONVERT(DECIMAL(5,2), SUM(AttemptGreen) * 100.0 / CASE WHEN (SUM(AttemptGreen) + SUM(Attempt)) = 0 THEN NULL ELSE (SUM(AttemptGreen) + SUM(Attempt)) END ),'%') as [percentage]
FROM tblTrackQuestErrors WHERE Wave=@Wave and Document_Number=@Document_Number group by Citrix_ID,Wave,Document_Number
answered Jan 21 at 6:38
Sudhakar ChitlamSudhakar Chitlam
72
add a comment |
SELECT Citrix_ID, Wave,Document_Number, SUM(Attempt) as Incorrect,SUM(AttemptGreen) as Correct,
CONCAT(CONVERT(DECIMAL(5,2), SUM(AttemptGreen) * 100.0 / CASE WHEN (SUM(AttemptGreen) + SUM(Attempt)) = 0 THEN NULL ELSE (SUM(AttemptGreen) + SUM(Attempt)) END ),'%') as [percentage]
FROM tblTrackQuestErrors WHERE Wave=@Wave and Document_Number=@Document_Number group by Citrix_ID,Wave,Document_Number
answered Jan 21 at 6:38
Sudhakar ChitlamSudhakar Chitlam
72
SELECT Citrix_ID, Wave,Document_Number, SUM(Attempt) as Incorrect,SUM(AttemptGreen) as Correct,
CONCAT(CONVERT(DECIMAL(5,2), SUM(AttemptGreen) * 100.0 / CASE WHEN (SUM(AttemptGreen) + SUM(Attempt)) = 0 THEN NULL ELSE (SUM(AttemptGreen) + SUM(Attempt)) END ),'%') as [percentage]
FROM tblTrackQuestErrors WHERE Wave=@Wave and Document_Number=@Document_Number group by Citrix_ID,Wave,Document_Number
answered Jan 21 at 6:38
Sudhakar ChitlamSudhakar Chitlam
72
answered Jan 21 at 6:38
Sudhakar ChitlamSudhakar Chitlam
72
answered Jan 21 at 6:38
Sudhakar ChitlamSudhakar Chitlam
72
answered Jan 21 at 6:38
Sudhakar ChitlamSudhakar Chitlam
72
72
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%2f54266512%2fcalculate-separate-percentage-value-for-multiple-ids-in-sql%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
