[askdba] Need help with SQL

  • From: VuCanDo <vuquyen@xxxxxxxxx>
  • To: ASKDBA <askdba@xxxxxxxxxxxxx>
  • Date: Wed, 2 Mar 2005 11:06:14 -0800

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

Other related posts: