CODEWITHSUNDEEP
sqloracle-database
C.Ward
C.Ward

Reputation: 85

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;

Upvotes: 0

Views: 43

Answers (2)

venkat
venkat

Reputation: 46

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;

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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;

Upvotes: 1

Related Questions