1

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

  • In your outermost select any column that is not an aggregate function must be also in the group by clause. – mustaccio Oct 5 '16 at 18:48
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.