1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Aggregate function in SQL Server

Discussion in 'Other Databases' started by sowmyav, Jan 15, 2009.

  1. sowmyav

    sowmyav Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

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

    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
    Code (Text):

     "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
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Re: Aggregate function

    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.

    Code (Text):

    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 .

    Code (Text):

    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 .
     
  3. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Moved this to the "Other Databases" Section! :)
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Hmhm.. that is the perfect section for this post.
     
  5. sowmyav

    sowmyav Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Thank you so much for your valuable reply.