Database:
Course code final direction in local currency the
001 through 100
001 through 100
001
100 loanBorrow 50 001
002 through 100
003 the goods 100

Hope is the result of the
Subject code amount
50 001
002, 100,
003-100

My code is:
Select course code, b. (b. borrow - c. loans) as amount from
(SELECT course code, the sum (local currency final number) as borrow from subject balance _ERP where the final direction="borrow" group by course code) as b,
(SELECT course code, the sum (local currency final number) as number of loans from subject balance _ERP where the final direction="credit" group by course code) as c
Where b. course code=c. course code

The current difficulty is some subject code in the corresponding records only borrow or only,
If borrow and lend all exist, the data is correct, but only borrow or only when the goods can't search the record came out, I hope there is no data to 0, then borrow a few - credit number

CodePudding user response:

The select course code, course name, sum (iif (="borrow" the final direction, amount, amount)) as the amount of the from subject balance _ERP group by course code

The focus is on using the IIF in Sum function

CodePudding user response:

SELECT course code, the sum (iif (="borrow" the final direction, amount, amount of - 1 *)) as the from subject balance _ERP aggregate amount of group by course code