Attached are two select queries. The first long query is the one that I need help with. The second short query is a "select COUNT" statement that I need to embed in my long query. Let me explain.
The short query is as follows: select COUNT (aaSubLedgerHdrID) from AAG20003. In my long query, I have the following: OAKVA..AAG20001.DEBITAMT as [Debit].
I want to take this Debit field and divide it by the COUNT "amount". In my example, the Debit amount is 2400 and the COUNT is 6. So, I should get 400.
After trying this and after running my long query, however, I get the following error:
Column 'OAKVA..GL20000.JRNENTRY' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I am not so sure that I want to do a bunch of GROUP BYs for my query. I simply want to divide that Debit amount by that COUNT amount.
Is there any special syntax that I can use to accomplish what should be a simple feat?
Thanks, and much appreciated!
select DISTINCT --OAKVA..AAG00401.aaTrxDimCode, OAKVA..AAG00401.aaTrxDimCodeDescr,
OAKVA..GL20000.JRNENTRY as [DocumentNumber], OAKVA..PM30200.DOCDATE as [DocumentDate], OAKVA..GL20000.TRXDATE as [PostDate],
LEFT(OAKVA..RM00101.CUSTNMBR, 3) as [ClientCode], OAKVA..RM00101.CUSTNMBR as [ClientNumber],
OAKVA..RM00101.CUSTNAME as [ClientName],
OAKVA..GL20000.SOURCDOC as [DocumentType],
0 as [Sales], 0 as [Tax],
0 as [Total1],
OAKVA..GL20000.TRXSORCE as [Source],
OAKVA..GL00105.ACTNUMST as [PostingAccount],
RIGHT(rtrim(OAKVA..GL00105.ACTNUMST), 6) as [NaturalAccount], OAKVA..GL00100.ACTDESCR as [Description],
--OAKVA..ME50405.USERDEF1 as [PropertyManagement],
OAKVA..AAG20001.DEBITAMT as [Debit], OAKVA..AAG20001.CRDTAMNT as [Credit],
OAKVA..AAG20001.CRDTAMNT - OAKVA..AAG20001.DEBITAMT as [Total2]
INNER JOIN OAKVA..AAG20003 on OAKVA..AAG00401.aaTrxDimCodeID = OAKVA..AAG20003.aaTrxCodeID
INNER JOIN OAKVA..AAG20001 on OAKVA..AAG20001.aaSubLedgerHdrID = OAKVA..AAG20003.aaSubLedgerHdrID
and OAKVA..AAG20001.aaSubLedgerDistID = OAKVA..AAG20003.aaSubLedgerDistID
INNER JOIN OAKVA..AAG20000 on OAKVA..AAG20001.aaSubLedgerHdrID = OAKVA..AAG20000.aaSubLedgerHdrID
INNER JOIN OAKVA..GL20000 on OAKVA..AAG20000.DOCNUMBR = OAKVA..GL20000.ORCTRNUM
INNER JOIN OAKVA..PM30200 on OAKVA..GL20000.ORCTRNUM = OAKVA..PM30200.VCHRNMBR
INNER JOIN OAKVA..GL00105 on OAKVA..GL20000.ACTINDX = OAKVA..GL00105.ACTINDX
INNER JOIN OAKVA..GL00100 on OAKVA..GL00105.ACTINDX = OAKVA..GL00100.ACTINDX
INNER JOIN OAKVA..RM00101 on OAKVA..AAG00401.aaTrxDimCode = OAKVA..RM00101.CUSTNMBR
INNER JOIN OAKVA..ME50405 on OAKVA..RM00101.CUSTNMBR = OAKVA..ME50405.CUSTNMBR
where OAKVA..AAG00401.aaTrxDimCodeID = OAKVA..AAG20003.aaTrxCodeID
--and OAKVA..AAG20003.aaSubLedgerHdrID = '322047'
and SUBSTRING(OAKVA..GL00105.ACTNUMST, CHARINDEX('-', ACTNUMST) + 1, 1) = '4'
--and OAKVA..AAG20000.SERIES = '4'
--and OAKVA..AAG20001.aaCustID = '' and OAKVA..AAG20001.aaVendID <> ''
and OAKVA..GL20000.TRXDATE BETWEEN '2011-08-01 00:00:00.000' and '2011-08-31 00:00:00.000'
and OAKVA..GL20000.SOURCDOC = 'PMTRX'
select COUNT (aaSubLedgerHdrID) from AAG20003