SQL query avg function not working

group byoracle

This query produces the correct data :

select 
       extract (hour from (tr.eventts-pr.eventts))*3600+
       extract (minute from (tr.eventts-pr.eventts))*60+
       extract (second from(tr.eventts-pr.eventts))*1000 ms
,pr.pid
from (      select max((FROM_TZ(CAST(eventts AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific')) eventts, pid
            from WC_PACKAGEREPORT
            where DESTINATION=154
             AND PID in(SELECT unique pid FROM WC_PACKAGEINFO 
                        WHERE EXECUTEDTASK='ManualScan'  
                        and (FROM_TZ(CAST(eventts AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') >= (FROM_TZ(CAST(trunc(SYSDATE) AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific')
          )
group by pid) pr

            inner join (  

select max((FROM_TZ(CAST(eventts AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific')) eventts, pid
from WC_TRACKINGREPORT
where areaid=1444
and zoneid=68
and equipmentid=03
group by pid) tr 

on tr.pid=pr.pid;

I now want to get the average ms taken so i try :

select 
       avg(extract (hour from (tr.eventts-pr.eventts))*3600+
       extract (minute from (tr.eventts-pr.eventts))*60+
       extract (second from(tr.eventts-pr.eventts))*1000) ms
,pr.pid
from (      select max((FROM_TZ(CAST(eventts AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific')) eventts, pid
            from WC_PACKAGEREPORT
            where DESTINATION=154
             AND PID in(SELECT unique pid FROM WC_PACKAGEINFO 
                        WHERE EXECUTEDTASK='ManualScan'  
                        and (FROM_TZ(CAST(eventts AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') >= (FROM_TZ(CAST(trunc(SYSDATE) AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific')
          )
group by pid) pr

            inner join (  

select max((FROM_TZ(CAST(eventts AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific')) eventts, pid
from WC_TRACKINGREPORT
where areaid=1444
and zoneid=68
and equipmentid=03
group by pid) tr 

on tr.pid=pr.pid;

but i get a "not a single-group group function".

I believe I need to wrap the sql somewhere but I'm not sure where. Can any of you advise?

Tom

Best Answer

At the outermost level, you are returning the average values along with pr.pid. If you want averages per pid, then you need to group your rows by pid.

You are doing that in the nested queries but not in the main one. So, after on tr.pid=pr.pid just add group by pr.pid.

But if you want the average value across the entire row set, remove pr.pid from the (main) select list.

Related Question