ORA-00937Group by / avg problem [message #199152] |
Fri, 20 October 2006 11:00  |
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 #199156 is a reply to message #199152] |
Fri, 20 October 2006 11:53  |
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
|
|
|