Arithmetic operation on zero
LedgerId AccountId EntryType Debit Credit
2 2 D 50000.00 NULL
3 2 D 10000.00 NULL
4 2 C NULL 25000.00
6 2 C NULL 10000.00
7 2 D 89000.00 NULL
8 2 D 89000.00 NULL
10 3 D 715871.00 NULL
The following query computes Balance
:
Select Accounts.ID [AccountID],Name,AccountType [AccountType], SUM(Debit) - SUM(Credit) [Balance] FROM Accounts
join Dealers on Accounts.DealerId = Dealers.ID
join Ledger on Accounts.ID = Ledger.AccountId
GROUP BY Accounts.ID, Name, AccountType
and it returns:
AccountID Name AccountType Balance
2 Mateen P 203000.00
3 Shery P NULL
expected output:
AccountID Name AccountType Balance
2 Mateen P 203000.00
3 Shery P 715871.00
Balance
for account 3 is Null, it returns null
when something needs to be subtracted from null
.
Example:
select 5 - NULL
returns null
.
Question: Now how can I get Balance
instead null
?
source to share
try this using isnull (value, 0), it will accept null values as 0
Select Accounts.ID [AccountID],Name,AccountType [AccountType],
SUM(isnull( Debit,0)) - SUM(isnull(Credit,0)) isnull([Balance],0) as
Balance FROM Accounts
join Dealers on Accounts.DealerId = Dealers.ID
join Ledger on Accounts.ID = Ledger.AccountId
GROUP BY Accounts.ID, Name, AccountType
source to share
The problem with NULL values, the SUM aggregate function ignores NULL values. The Coalesce function will replace it with 0 if NULL values are found.
Select Accounts.ID [AccountID],
Name,
AccountType [AccountType],
SUM(coalesce(Debit,0)) - SUM(coalesce(Credit,0)) [Balance]
FROM Accounts
join Dealers on Accounts.DealerId = Dealers.ID
join Ledger on Accounts.ID = Ledger.AccountId
GROUP BY Accounts.ID, Name, AccountType
source to share