Aggregate function in SQL Server

[sowmyav]

Hi,

Please find the below query in sql server.I need to get the sum of the field.The below qry is working fine.

select (arr.PROD),
arr.Calendar_Month_N,arr.fld_inv,
(arr.Fld_Inv/sum(arr.Fld_Inv)) as cum_fld,
((arr.Fld_Inv/sum(arr.Fld_Inv))*(arr.ARR_3MRA))as fldinv_calc,
 arr.ARR_3MRA
from DRV_Data_200811 arr 
 JOIN Test_Smry_200811 NTF 
ON (ARR.Prod=NTF.Prod And ARR.Calendar_Month=NTF.Calendar_Month)
group by ARR.PRODUCT,arr.Calendar_Month_N,arr.ARR_3MRA,arr.fld_inv
 
************************************

But I need to get one more summation which is

(sum((arr.Fld_Inv/sum(arr.Fld_Inv))*(arr.ARR_3MRA))) as glob in the select field.

but I am getting error as

 "Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression 
containing an aggregate or a subquery."

Could any one please help me on the same

[rajavu]

Hi Sowmya,

Neither this is SQL SERVER forum , Nor I am Sql Server expert.
Anyway we can reproduce this error in Oracle also.

See the following examples.

SQL> SELECT DEPTNO , COMM ,COMM/SUM(SAL) X
  2  FROM EMP2
  3  GROUP BY DEPTNO , COMM;
 
    DEPTNO       COMM          X
---------- ---------- ----------
        20        450      .5625
        30        500 .181818182
        30        700 .318181818
        10        300 .047619048
        20        400 .056537102
        30        300  .06741573
        10        500 .204081633
        20        100 .033333333
 
8 rows selected.
 
 SQL> SELECT DEPTNO ,COMM , SUM(COMM/SUM(SAL)) X
  2  FROM EMP2
  3  GROUP BY DEPTNO , COMM;
SELECT DEPTNO ,COMM , SUM(COMM/SUM(SAL)) X
       *
ERROR at line 1:
ORA-00937: not a single-group group function

Now we can sum the x again using an outer sub query as below .

SQL> SELECT DEPTNO, SUM(X) X
  2  FROM
  3  (SELECT DEPTNO , COMM ,COMM/SUM(SAL)X
  4  FROM EMP2
  5  GROUP BY DEPTNO , COMM )
  6  GROUP BY DEPTNO ;
 
    DEPTNO          X
---------- ----------
        30  .56741573
        20 .652370436
        10  .25170068
 
 SQL>

You can do the same in SQL Server .i guess.

I dont know whether Cub-oracle support non-oracle related posts.

Anyway , your are lucky to get the reply .

[Sadik]

Moved this to the "Other Databases" Section!

[rajavu]

Hmhm.. that is the perfect section for this post.

[sowmyav]

Thank you so much for your valuable reply.