Help with Aggregate query

[eerich]

Hello

I'm trying to reconcile a list of transactional GL data against a account balance summary file and I run into an error noting that I'm attempting to use two fields (TbBegBal and TbEndBal) that are not part of the aggregate function. Since I have both fields within my aggregate functions, I don't understand why I'm receiving the error. Could it be the order in which they are appearing in the aggregate? I've tried rearranging it but still received the error. My query is below. Any help is appreciated.

SELECT
GlAcct,
sum(IIf(GLAmount>0,GLAmount,0)) AS GL_DRActivity,
sum(IIf(GLAmount<0,GLAmount,0)) AS GL_CRActivity,
Round(Sum(GLAmount) + TBBegBal,0) AS GL_Q4EndBal,
Round((Sum(GLAmount)+ TBBegBal)- TBEndBal,0) AS AcctBalRecon
FROM corp LEFT JOIN Tbal ON corp.GLAcct=Tbal.TbAcct
GROUP BY corp.GLAcct;

 

[AlexCuse]

BegBal and EndBal are NOT included in your aggregate function. You could try taking an average or max for them, or just adding the two columns to your group by.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[eerich]

So I'm required to have TbBegBal and TbEndBal in the group by line even though i'm using them just for the addition and subtraction? I don't want to have them in the group by if possible, b/c i'd only like to group on the account number field. I don't think I've understood this function very well.

 

[AlexCuse]

Well, you can use max(tbBegBal) or avg(tbBegBal). But if there is only one possible value it would be easiest to just put them into your group by so you can avoid another function call.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[eerich]

Thanks.