[askdba] Re: Need help with SQL
- From: Ganesh Raja <ganesh.raja@xxxxxxxxx>
- To: askdba@xxxxxxxxxxxxx
- Date: Wed, 2 Mar 2005 22:46:51 +0000
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
>
>
Other related posts:
