Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00937Group by / avg problem
ORA-00937Group by / avg problem [message #199152] Fri, 20 October 2006 11:00 Go to next message
welchaz
Messages: 23
Registered: April 2005
Location: Tucson, AZ
Junior Member
I have the following query:

select c.tp_job, c.cd_wr, a.ts_change, max(b.ts_change),
(trunc(max(b.ts_change)) - trunc(a.ts_change)) days
from audit_table a, audit_table b, wr_table c
where c.tp_job in ('DUD','MOD')
and a.ts_change >= sysdate - 180
and (a.cd_status = '00' and upper(a.nm_function) = 'WRCREATE')
and (b.cd_status = '90' and b.nm_function='MILERQMT')
and a.cd_wr = b.cd_wr
and b.cd_wr = c.cd_wr
and c.cd_dist <> 'STCZ'
group by c.tp_job, c.cd_wr, a.ts_change
order by tp_job, cd_wr;

TP_JOB CD_WR TS_CHANGE MAX(B.TS_ DAYS
---------- ---------- --------- --------- ----------
DUD 163127 26-MAY-06 18-JUL-06 53
DUD 163146 26-MAY-06 23-AUG-06 89
DUD 163171 28-MAY-06 13-JUL-06 46
DUD 163267 30-MAY-06 23-AUG-06 85
DUD 163282 31-MAY-06 22-AUG-06 83
DUD 163327 31-MAY-06 13-JUL-06 43
DUD 163654 06-JUN-06 13-JUL-06 37
DUD 164055 14-JUN-06 29-AUG-06 76
DUD 164218 16-JUN-06 30-AUG-06 75
DUD 165163 01-JUL-06 14-AUG-06 44
MOD 161427 25-APR-06 05-MAY-06 10
MOD 161610 28-APR-06 19-MAY-06 21
MOD 162165 08-MAY-06 22-MAY-06 14
MOD 162449 12-MAY-06 16-JUN-06 35
MOD 162478 13-MAY-06 25-MAY-06 12
MOD 162538 16-MAY-06 17-AUG-06 93
MOD 162690 18-MAY-06 18-MAY-06 0
MOD 162695 18-MAY-06 18-JUL-06 61


I need to take these results, and show the average of the days per job type, so that the results look like this:

TP_JOB AVG_DAYS
DUD 63
MOD 31

This is the modification of the query I tried to get this, and the error I'm getting:

select c.tp_job, avg((trunc(max(b.ts_change)) - trunc(a.ts_change))) avg_days
from audit_table a, audit_table b, wr_table c
where c.tp_job in ('DUD','MOD')
and a.ts_change >= sysdate - 180
and (a.cd_status = '00' and upper(a.nm_function) = 'WRCREATE')
and (b.cd_status = '90' and b.nm_function='MILERQMT')
and a.cd_wr = b.cd_wr
and b.cd_wr = c.cd_wr
and c.cd_dist <> 'STCZ'
group by c.tp_job
order by tp_job;

select c.tp_job, avg((trunc(max(b.ts_change)) - trunc(a.ts_change))) avg_days
*
ERROR at line 1:
ORA-00937: not a single-group group function


I am grouping by the single item c.tp_job, so I'm not sure why I'm getting this error. I think I have a bigger logic problem here with my averaging, but can't quite put my finger on it. Thanks in advance for your help.


Re: ORA-00937Group by / avg problem [message #199154 is a reply to message #199152] Fri, 20 October 2006 11:42 Go to previous messageGo to next message
Mike Bentley
Messages: 8
Registered: September 2006
Junior Member
The error is coming from the use of a.ts_change, which is only truncated and does not have a group function applied to it. In the original query you use this in the GROUP BY clause.
Re: ORA-00937Group by / avg problem [message #199156 is a reply to message #199152] Fri, 20 October 2006 11:53 Go to previous message
welchaz
Messages: 23
Registered: April 2005
Location: Tucson, AZ
Junior Member
Thanks for the thought, but adding a.ts_change to the GROUP BY isn't helping:

select c.tp_job, avg((trunc(max(b.ts_change)) - trunc(a.ts_change))) avg_days
2 from audit_table a, audit_table b, wr_table c
3 where c.tp_job in ('DUD','MOD')
4 and a.ts_change >= sysdate - 180
5 and (a.cd_status = '00' and upper(a.nm_function) = 'WRCREATE')
6 and (b.cd_status = '90' and b.nm_function='MILERQMT')
7 and a.cd_wr = b.cd_wr
8 and b.cd_wr = c.cd_wr
9 and c.cd_dist <> 'STCZ'
10 group by c.tp_job, a.ts_change
11 order by tp_job;
select c.tp_job, avg((trunc(max(b.ts_change)) - trunc(a.ts_change))) avg_days
*
ERROR at line 1:
ORA-00937: not a single-group group function
Previous Topic: About ORA-01031: insufficient privileges Error
Next Topic: Help with Stored Procedure
Goto Forum:
  


Current Time: Fri Dec 02 21:09:25 CST 2016

Total time taken to generate the page: 0.10770 seconds