Sum function in Where clause
506799Apr 17 2006 — edited Apr 17 2006I am trying to return all rows with a 0 value for sum(a.posted_total_amt)
I've tried the following:
This returns no rows. However, I know there is data which should be returned.
SELECT A.BUSINESS_UNIT, A.ACCOUNT, sum(A.POSTED_TOTAL_AMT)
FROM PS_LEDGER A
WHERE A.FISCAL_YEAR = 2006
AND EXISTS (SELECT 'X' FROM PSTREESELECT05 A3_1 WHERE A3_1.SELECTOR_NUM=239846 AND A.BUSINESS_UNIT=A3_1.RANGE_FROM_05)
AND A.ACCOUNTING_PERIOD IN (0,1,2,3,998)
AND EXISTS (SELECT 'X' FROM PSTREESELECT10 A3_3 WHERE A3_3.SELECTOR_NUM=239847 AND A.ACCOUNT>= A3_3.RANGE_FROM_10 AND A.ACCOUNT <= A3_3.RANGE_TO_10)
AND A.STATISTICS_CODE = ' '
AND A.ACCOUNT NOT IN ('1401','1402','1403')
group by A.BUSINESS_UNIT, A.ACCOUNT
having sum(A.POSTED_TOTAL_AMT) = 0
The following error is returned by the statement below
ERROR at line 10:
ORA-00934: group function is not allowed here
SELECT A.BUSINESS_UNIT, A.ACCOUNT, sum(A.POSTED_TOTAL_AMT)
FROM PS_LEDGER A
WHERE A.FISCAL_YEAR = 2006
AND EXISTS (SELECT 'X' FROM PSTREESELECT05 A3_1 WHERE A3_1.SELECTOR_NUM=239846 AND A.BUSINESS_UNIT=A3_1.RANGE_FROM_05)
AND A.ACCOUNTING_PERIOD IN (0,1,2,3,998)
AND EXISTS (SELECT 'X' FROM PSTREESELECT10 A3_3 WHERE A3_3.SELECTOR_NUM=239847 AND A.ACCOUNT>= A3_3.RANGE_FROM_10 AND A.ACCOUNT <= A3_3.RANGE_TO_10)
AND A.STATISTICS_CODE = ' '
AND A.ACCOUNT NOT IN ('1401','1402','1403')
and sum(A.POSTED_TOTAL_AMT) = 0
group by A.BUSINESS_UNIT, A.ACCOUNT
Any guidance would be much appreciated