Using Group By while Counting a Different Columns Values
I am trying to group by job id while having it count the milliseconds in each row of the job id.
For example ill have 5 rows of job 55 all with different milliseconds like .3, .5, 1.5, .3, .4. I don't want it to display 5 rows, only one like this: 55 | 3.0
This is how far i've gotten i just need a little nudge int eh right direction.
SELECT job_ID, AVG_REC_TIME_MILLIS, count(AVG_REC_TIME_MILLIS)
FROM job_step js
GROUP BY js.JOB_ID;
sql oracle
asked Nov 21 '18 at 15:33
C.WardC.Ward
197
add a comment |
I am trying to group by job id while having it count the milliseconds in each row of the job id.
For example ill have 5 rows of job 55 all with different milliseconds like .3, .5, 1.5, .3, .4. I don't want it to display 5 rows, only one like this: 55 | 3.0
This is how far i've gotten i just need a little nudge int eh right direction.
SELECT job_ID, AVG_REC_TIME_MILLIS, count(AVG_REC_TIME_MILLIS)
FROM job_step js
GROUP BY js.JOB_ID;
sql oracle
asked Nov 21 '18 at 15:33
C.WardC.Ward
197
3
Hint :SUMinstead ofCOUNT().
– Yogesh Sharma
Nov 21 '18 at 15:34
Could you please more elaborate with table format data
– Sahil Anand
Nov 21 '18 at 15:36
add a comment |
I am trying to group by job id while having it count the milliseconds in each row of the job id.
For example ill have 5 rows of job 55 all with different milliseconds like .3, .5, 1.5, .3, .4. I don't want it to display 5 rows, only one like this: 55 | 3.0
This is how far i've gotten i just need a little nudge int eh right direction.
SELECT job_ID, AVG_REC_TIME_MILLIS, count(AVG_REC_TIME_MILLIS)
FROM job_step js
GROUP BY js.JOB_ID;
sql oracle
asked Nov 21 '18 at 15:33
C.WardC.Ward
197
I am trying to group by job id while having it count the milliseconds in each row of the job id.
For example ill have 5 rows of job 55 all with different milliseconds like .3, .5, 1.5, .3, .4. I don't want it to display 5 rows, only one like this: 55 | 3.0
This is how far i've gotten i just need a little nudge int eh right direction.
SELECT job_ID, AVG_REC_TIME_MILLIS, count(AVG_REC_TIME_MILLIS)
FROM job_step js
GROUP BY js.JOB_ID;
sql oracle
sql oracle
asked Nov 21 '18 at 15:33
C.WardC.Ward
197
asked Nov 21 '18 at 15:33
C.WardC.Ward
197
asked Nov 21 '18 at 15:33
C.WardC.Ward
197
asked Nov 21 '18 at 15:33
C.WardC.Ward
197
asked Nov 21 '18 at 15:33
C.WardC.Ward
197
197
3
Hint :SUMinstead ofCOUNT().
– Yogesh Sharma
Nov 21 '18 at 15:34
Could you please more elaborate with table format data
– Sahil Anand
Nov 21 '18 at 15:36
add a comment |
3
Hint :SUMinstead ofCOUNT().
– Yogesh Sharma
Nov 21 '18 at 15:34
Could you please more elaborate with table format data
– Sahil Anand
Nov 21 '18 at 15:36
3
3
Hint :
SUM instead of COUNT().– Yogesh Sharma
Nov 21 '18 at 15:34
Hint :
SUM instead of COUNT().– Yogesh Sharma
Nov 21 '18 at 15:34
Could you please more elaborate with table format data
– Sahil Anand
Nov 21 '18 at 15:36
Could you please more elaborate with table format data
– Sahil Anand
Nov 21 '18 at 15:36
add a comment |
2 Answers
2
active
oldest
votes
You need SUM() instead of COUNT() & SELECT statement should contains only columns which are aggregated when GROUP BY involved so, AVG_REC_TIME_MILLIS doesn't make any sense.
So, removed it from SELECT statement :
SELECT job_ID, SUM(AVG_REC_TIME_MILLIS)
FROM job_step js
GROUP BY js.JOB_ID;
answered Nov 21 '18 at 15:40
Yogesh SharmaYogesh Sharma
34.3k51440
Thanks! this is exactly what im looking for!
– C.Ward
Nov 21 '18 at 15:46
add a comment |
If you need to get the sum of all milliseconds for a job_id then
SELECT job_ID, sum(AVG_REC_TIME_MILLIS)
FROM job_step js
GROUP BY js.JOB_ID;
If number of job steps is what you need then consider the following query
SELECT job_ID, count(AVG_REC_TIME_MILLIS)
FROM job_step js
GROUP BY js.JOB_ID;
answered Nov 21 '18 at 15:46
venkatvenkat
365
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%2f53415468%2fusing-group-by-while-counting-a-different-columns-values%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
You need SUM() instead of COUNT() & SELECT statement should contains only columns which are aggregated when GROUP BY involved so, AVG_REC_TIME_MILLIS doesn't make any sense.
So, removed it from SELECT statement :
SELECT job_ID, SUM(AVG_REC_TIME_MILLIS)
FROM job_step js
GROUP BY js.JOB_ID;
answered Nov 21 '18 at 15:40
Yogesh SharmaYogesh Sharma
34.3k51440
Thanks! this is exactly what im looking for!
– C.Ward
Nov 21 '18 at 15:46
add a comment |
You need SUM() instead of COUNT() & SELECT statement should contains only columns which are aggregated when GROUP BY involved so, AVG_REC_TIME_MILLIS doesn't make any sense.
So, removed it from SELECT statement :
SELECT job_ID, SUM(AVG_REC_TIME_MILLIS)
FROM job_step js
GROUP BY js.JOB_ID;
answered Nov 21 '18 at 15:40
Yogesh SharmaYogesh Sharma
34.3k51440
Thanks! this is exactly what im looking for!
– C.Ward
Nov 21 '18 at 15:46
add a comment |
You need SUM() instead of COUNT() & SELECT statement should contains only columns which are aggregated when GROUP BY involved so, AVG_REC_TIME_MILLIS doesn't make any sense.
So, removed it from SELECT statement :
SELECT job_ID, SUM(AVG_REC_TIME_MILLIS)
FROM job_step js
GROUP BY js.JOB_ID;
answered Nov 21 '18 at 15:40
Yogesh SharmaYogesh Sharma
34.3k51440
You need SUM() instead of COUNT() & SELECT statement should contains only columns which are aggregated when GROUP BY involved so, AVG_REC_TIME_MILLIS doesn't make any sense.
So, removed it from SELECT statement :
SELECT job_ID, SUM(AVG_REC_TIME_MILLIS)
FROM job_step js
GROUP BY js.JOB_ID;
answered Nov 21 '18 at 15:40
Yogesh SharmaYogesh Sharma
34.3k51440
answered Nov 21 '18 at 15:40
Yogesh SharmaYogesh Sharma
34.3k51440
answered Nov 21 '18 at 15:40
Yogesh SharmaYogesh Sharma
34.3k51440
answered Nov 21 '18 at 15:40
Yogesh SharmaYogesh Sharma
34.3k51440
34.3k51440
Thanks! this is exactly what im looking for!
– C.Ward
Nov 21 '18 at 15:46
add a comment |
Thanks! this is exactly what im looking for!
– C.Ward
Nov 21 '18 at 15:46
Thanks! this is exactly what im looking for!
– C.Ward
Nov 21 '18 at 15:46
Thanks! this is exactly what im looking for!
– C.Ward
Nov 21 '18 at 15:46
add a comment |
If you need to get the sum of all milliseconds for a job_id then
SELECT job_ID, sum(AVG_REC_TIME_MILLIS)
FROM job_step js
GROUP BY js.JOB_ID;
If number of job steps is what you need then consider the following query
SELECT job_ID, count(AVG_REC_TIME_MILLIS)
FROM job_step js
GROUP BY js.JOB_ID;
answered Nov 21 '18 at 15:46
venkatvenkat
365
add a comment |
If you need to get the sum of all milliseconds for a job_id then
SELECT job_ID, sum(AVG_REC_TIME_MILLIS)
FROM job_step js
GROUP BY js.JOB_ID;
If number of job steps is what you need then consider the following query
SELECT job_ID, count(AVG_REC_TIME_MILLIS)
FROM job_step js
GROUP BY js.JOB_ID;
answered Nov 21 '18 at 15:46
venkatvenkat
365
add a comment |
If you need to get the sum of all milliseconds for a job_id then
SELECT job_ID, sum(AVG_REC_TIME_MILLIS)
FROM job_step js
GROUP BY js.JOB_ID;
If number of job steps is what you need then consider the following query
SELECT job_ID, count(AVG_REC_TIME_MILLIS)
FROM job_step js
GROUP BY js.JOB_ID;
answered Nov 21 '18 at 15:46
venkatvenkat
365
If you need to get the sum of all milliseconds for a job_id then
SELECT job_ID, sum(AVG_REC_TIME_MILLIS)
FROM job_step js
GROUP BY js.JOB_ID;
If number of job steps is what you need then consider the following query
SELECT job_ID, count(AVG_REC_TIME_MILLIS)
FROM job_step js
GROUP BY js.JOB_ID;
answered Nov 21 '18 at 15:46
venkatvenkat
365
answered Nov 21 '18 at 15:46
venkatvenkat
365
answered Nov 21 '18 at 15:46
venkatvenkat
365
answered Nov 21 '18 at 15:46
venkatvenkat
365
365
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%2f53415468%2fusing-group-by-while-counting-a-different-columns-values%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
3
Hint :
SUMinstead ofCOUNT().– Yogesh Sharma
Nov 21 '18 at 15:34
Could you please more elaborate with table format data
– Sahil Anand
Nov 21 '18 at 15:36