[askdba] Re: Need help with SQL

  • From: VuCanDo <vuquyen@xxxxxxxxx>
  • To: askdba@xxxxxxxxxxxxx
  • Date: Thu, 3 Mar 2005 10:20:33 -0800

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
> >
> >
> 
>

Other related posts: