Thanks Ganesh. It's working now...... On Wed, 2 Mar 2005 22:46:51 +0000, Ganesh Raja <ganesh.raja@xxxxxxxxx> wrote: > A Grouping func can only be given in the having clause. > > HTH > > select RTRIM(PROV_ID), > RTRIM(STDDEV_CLAIM_PCT), > RTRIM(STDDEV_DOL_PCT), > RTRIM(PROC_GRP), > RTRIM(m.proc_cd) as PROC_CD, > LTRIM(TO_CHAR(COUNT(m.PROC_CD), '999,999,999,999')) as NUM_SRV, > LTRIM(TO_CHAR(SUM(PAID_AMT), '$999,999,999,999.99')) as DOLLARS, > LTRIM(TO_CHAR((COUNT(m.PROC_CD) / 15780) * 100, > '999,999,999,999.9')) as PCT_SRV, > LTRIM(TO_CHAR((SUM(PAID_AMT) / 437780.94000000006) * 100, > '999,999,999,999.9')) as PCT_DOL, > LTRIM(TO_CHAR(avg(PEER_CT), '999,999,999,999')) as PROV_CT, > LTRIM(TO_CHAR(AVG_CLAIM_PCT, '999,999,999,999.99')), > LTRIM(TO_CHAR(((COUNT(m.PROC_CD) / 15780) * 100 - AVG_CLAIM_PCT) / > STDDEV_CLAIM_PCT, > '999,999,999,999.99')) as Z_CLAIM_PCT, > LTRIM(TO_CHAR(AVG_DOL_PCT, '999,999,999,999.99')), > LTRIM(TO_CHAR(((SUM(PAID_AMT) / 437780.94000000006) * 100 - > AVG_DOL_PCT) / STDDEV_DOL_PCT, > '999,999,999,999.99')) as Z_DOL_PCT > from spy_mart m, spy_proc_norm n > where SET_ID = '110' > AND PROV_ID = '111909101' > and m.proc_cd = n.proc_cd > and n.model_id = 'GENRIC04' > and n.peer_id = '30' > and (((COUNT(m.PROC_CD) / 15780) * 100 - AVG_CLAIM_PCT) / > STDDEV_CLAIM_PCT) > 2 > group by m.proc_cd, > PROC_GRP, > PROV_ID, > AVG_CLAIM_PCT, > AVG_DOL_PCT, > STDDEV_CLAIM_PCT, > STDDEV_DOL_PCT > Having > (((COUNT(m.PROC_CD) / 15780) * 100 - AVG_CLAIM_PCT) / > STDDEV_CLAIM_PCT) > 2 > order by NVL(((COUNT(m.PROC_CD) / 15780) * 100 - AVG_CLAIM_PCT) / > STDDEV_CLAIM_PCT, > -999999999) desc > > On Wed, 2 Mar 2005 11:06:14 -0800, VuCanDo <vuquyen@xxxxxxxxx> wrote: > > What is the best way to fix this SQL? What I did is to ADD another > > where clause below: > > and (((COUNT(m.PROC_CD) / 15780) * 100 - AVG_CLAIM_PCT)/STDDEV_CLAIM_PCT) > > > 2 > > into this SQL and keep getting group function is not allowed here. > > > > Thanks, > > > > Vu > > > > select RTRIM(PROV_ID), RTRIM(STDDEV_CLAIM_PCT), RTRIM(STDDEV_DOL_PCT), > > RTRIM(PROC_GRP), RTRIM(m.proc_cd) as PROC_CD, > > LTRIM(TO_CHAR(COUNT(m.PROC_CD), '999,999,999,999')) as NUM_SRV, > > LTRIM(TO_CHAR(SUM(PAID_AMT), '$999,999,999,999.99')) as DOLLARS, > > LTRIM(TO_CHAR((COUNT(m.PROC_CD) / 15780) * 100, '999,999,999,999.9')) > > as PCT_SRV, > > LTRIM(TO_CHAR((SUM(PAID_AMT) / 437780.94000000006) * 100, > > '999,999,999,999.9')) as PCT_DOL, > > LTRIM(TO_CHAR(avg(PEER_CT), '999,999,999,999')) as PROV_CT, > > LTRIM(TO_CHAR(AVG_CLAIM_PCT, '999,999,999,999.99')), > > LTRIM(TO_CHAR(((COUNT(m.PROC_CD) / 15780) * 100 - > > AVG_CLAIM_PCT)/STDDEV_CLAIM_PCT, '999,999,999,999.99')) as > > Z_CLAIM_PCT, > > LTRIM(TO_CHAR(AVG_DOL_PCT, '999,999,999,999.99')), > > LTRIM(TO_CHAR(((SUM(PAID_AMT) / 437780.94000000006) * 100 - > > AVG_DOL_PCT)/STDDEV_DOL_PCT, '999,999,999,999.99')) as Z_DOL_PCT > > from spy_mart m , spy_proc_norm n where SET_ID = '110' AND PROV_ID = > > '111909101' > > and m.proc_cd=n.proc_cd and n.model_id = 'GENRIC04' and n.peer_id='30' > > and (((COUNT(m.PROC_CD) / 15780) * 100 - AVG_CLAIM_PCT)/STDDEV_CLAIM_PCT) > > > 2 > > group by m.proc_cd, PROC_GRP, PROV_ID, AVG_CLAIM_PCT, AVG_DOL_PCT, > > STDDEV_CLAIM_PCT, STDDEV_DOL_PCT > > order by NVL(((COUNT(m.PROC_CD) / 15780) * 100 - > > AVG_CLAIM_PCT)/STDDEV_CLAIM_PCT, -999999999) desc > > > > > >