subtracting field in Table A from sum(field) in table B

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

subtracting field in Table A from sum(field) in table B

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-08-19 : 07:47:28

sue writes "I would like to subtract the sum of amounts for a particular ledger_account in Table B from the period_amount in the corresponding ledger_account in Table A. The system will not let me do this because the period_amount from table B is not part of the aggregate function or the group by clause.

This is what I am trying to do:
SELECT lb.period_amount- SUM(gl.amount) ,
lb.ledger_account
FROM gl_cost_transactions gl, ledger_balances lb
WHERE gl.period = 1
AND gl.fiscal_year = '2006'
and gl.ledger_account not like '%3016'
AND gl.period = lb.period
AND gl.document_id1 LIKE 'A-%'
AND gl.fiscal_year = lb.fiscal_year
AND gl.ledger_account = lb.ledger_account
GROUP BY lb.ledger_account

Is there any way around this problem?

Thanks,

Sue"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-19 : 08:07:35

You should also group it by period_amount also

Madhivanan

Failing to plan is Planning to fail

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-08-19 : 08:14:20

Assuming ledger_acccount is unique in ledger_balances table.

SELECT min(lb.period_amount)- SUM(gl.amount) ,
lb.ledger_account 
FROM gl_cost_transactions gl, ledger_balances lb 
WHERE gl.period = 1 
AND gl.fiscal_year = '2006' 
and gl.ledger_account not like '%3016'
AND gl.period = lb.period 
AND gl.document_id1 LIKE 'A-%' 
AND gl.fiscal_year = lb.fiscal_year 
AND gl.ledger_account = lb.ledger_account 
GROUP BY lb.ledger_account

or apply madhivanan's suggestion.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources