Using Group By while Counting a Different Columns Values

Question

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;

Hint : SUM instead of COUNT().

Yogesh Sharma
– Yogesh Sharma

2018-11-21 15:34:56 +00:00
Commented Nov 21, 2018 at 15:34 — Yogesh Sharma
– Yogesh Sharma, Commented Nov 21, 2018 at 15:34
Could you please more elaborate with table format data

Sahil Anand
– Sahil Anand

2018-11-21 15:36:17 +00:00
Commented Nov 21, 2018 at 15:36 — Sahil Anand
– Sahil Anand, Commented Nov 21, 2018 at 15:36

Yogesh Sharma · Accepted Answer · 2018-11-21 15:40:41Z

1

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, 2018 at 15:40

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

C.Ward Over a year ago

Thanks! this is exactly what im looking for!

venkat · Accepted Answer · 2018-11-21 15:46:57Z

0

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, 2018 at 15:46

venkat

466 bronze badges

Collectives™ on Stack Overflow

Using Group By while Counting a Different Columns Values

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related