Post reply

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

  • September 20, 2006 at 1:13 pm

    #172760

    When trying to execute the following query I received error message "Cannot perform an aggregate function on an expression containing an aggregate or a subquery." I'm trying to subtract the credit from the total amount Creating the Balance. Any help would be greatly appreciated.   

    SELECT

    SubString(a.sCode, 1 , 4) + '-'+SubString(a.sCode, 5 , 4) + ' (' + rtrim(a.sDesc) + ')',

    p.sCode,

    t.sdateoccurred,

    d.CashPost pdate,

    CASE isnull(t.hPerson,0) WHEN 0 THEN t.sUserDefined2 ELSE '(' + rtrim(r.uCode) + ') ' + r.uLastName END,

    'R-' + convert(varchar,d.hInvOrRec - 600000000),

    t.sUserDefined1,

    CASE sign(d.sAmount) WHEN -1 THEN abs(d.sAmount) ELSE 0 END Debit,

    CASE sign(d.sAmount) WHEN 1 THEN abs(d.sAmount) ELSE 0 END Credit,

    Sum((CASE sign(d.sAmount) WHEN 1 THEN abs(d.sAmount) ELSE 0 END) - (sum(isnull(tot.sBegin,0)))) Balance,

    t.sNotes

    FROM    detail d , Acct a, property p, Trans t

    left outer join Person r on (t.hPerson = r.hMy)

    WHERE   d.hInvOrRec = t.hMy

    AND CASE WHEN '0' in (1) THEN d.hAcct ELSE CASE WHEN isnull(d.hAccrualAcct,0) = 0 THEN d.hAcct ELSE d.hAccrualAcct END END = a.hMy

    and     d.hProp = p.hmy

    AND     a.sCode > '    '

    AND     d.hinvorrec BETWEEN 600000000 AND 699999999

    AND     t.itype = 6

    AND     ( '1' in ('1') or '0' in ('1') ) 

    AND p.hMy = 43

    AND d.cashPost Between convert(datetime, '01-Jan-2006') AND convert(datetime, '01-Aug-2006')

    AND a.scode >= (select scode from acct where hmy = 7151 )

    AND a.scode <= (select scode from acct where hmy = 7151 ) 

  • September 20, 2006 at 1:24 pm

    #661579

    You can do sum(sum))

    You need to put the first sum in a derived table, and then redo another sum on that.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply