Group and take average based on count-CodePudding

I have a table with three columns. I need to take average of a column(duration) based on the repetition of another column(phase).

id      phase duration
19743   D17   1.66            
19743   D06   2.25    
19743   C17   2.3         
19743   D06   4.44

In above data D06 have two entries, and instead of taking two I need to get the average of D06 in a single row along with other. The final output should be like this.

    id      phase duration
   19743    D17   1.66            
   19743    D06   3.35    
   19743    C17   2.3

CodePudding user response：

Can you use this query:

with table1 as (
                select 19743 as id, 'D17' as phase, 1.66 as duration from dual union all
                select 19743 as id, 'D06' as phase, 2.25 as duration from dual union all
                select 19743 as id, 'C17' as phase, 2.3  as duration from dual union all
                select 19743 as id, 'D06' as phase, 4.44 as duration from dual                 
               )
             select id, phase, round(avg(duration) ,2)
               from table1
              group by id, phase
              
        ID PHA ROUND(AVG(DURATION),2)
---------- --- ----------------------
     19743 D06                   3,35
     19743 D17                   1,66
     19743 C17                    2,3

Thank you

CodePudding user response：

I found an easy way to achieve this using a sub query. I am posting it here because it will be useful for someone else.

SELECT ID,PHASE,AVG(DURATION)
FROM (SELECT ID, DURATION,  PHASE
  FROM TR_TRANSACTION
  WHERE ID =19743
 )GROUP BY ID,PHASE;