How can I get this query to return 0 instead of null?

Question

I have this query:

SELECT (SUM(tblTransaction.AmountPaid) - SUM(tblTransaction.AmountCharged)) AS TenantBalance, tblTransaction.TenantID
    FROM tblTransaction
    GROUP BY tblTransaction.TenantID

But there's a problem with it; there are other TenantID's that don't have transactions and I want to get those too.

For example, the transaction table has 3 rows for bob, 2 row for john and none for jane. I want it to return the sum for bob and john AND return 0 for jane. (or possibly null if there's no other way)

How can I do this?

Tables are like this:

Tenants  
  ID  
  Other Data  
Transactions  
  ID  
  TenantID (fk to Tenants)
  Other Data  

Solution 1

(You didn't state your sql engine, so I'm going to link to the MySQL documentation).

This is pretty much exactly what the COALESCE() function is meant for. You can feed it a list, and it'll return the first non-null value in the list. You would use this in your query as follows:

SELECT COALESCE((SUM(tr.AmountPaid) - SUM(tr.AmountCharged)), 0) AS TenantBalance, te.ID
FROM tblTenant AS te
    LEFT JOIN tblTransaction AS tr ON (tr.TenantID = te.ID)
GROUP BY te.ID;

That way, if the SUM() result would be NULL, it's replaced with zero.

Edited: I rewrote the query using a LEFT JOIN as well as the COALESCE(), I think this is the key of what you were missing originally. If you only select from the Transactions table, there is no way to get information about things not in the table. However, by using a left join from the Tenants table, you should get a row for every existing tenant.

Solution 2

Below is a full walkthrough of the problem. The function isnull has also been included to ensure that a balance of zero (rather than null) is returned for Tenants with no transactions.

create table tblTenant
(
    ID int identity(1,1) primary key not null,
    Name varchar(100)
);

create table tblTransaction
(
    ID  int identity(1,1) primary key not null,
    tblTenantID int,
    AmountPaid  money,
    AmountCharged money
);

insert into tblTenant(Name)
select 'bob' union all select 'Jane' union all select 'john';

insert into tblTransaction(tblTenantID,AmountPaid, AmountCharged)
select 1,5.00,10.00
union all
select 1,10.00,10.00
union all
select 1,10.00,10.00
union all
select 2,10.00,15.00
union all 
select 2,15.00,15.00


select * from tblTenant
select * from tblTransaction

SELECT 
    tenant.ID, 
    tenant.Name,
    isnull(SUM(Trans.AmountPaid) - SUM(Trans.AmountCharged),0) AS Balance 
FROM tblTenant tenant
    LEFT JOIN tblTransaction Trans ON 
        tenant.ID = Trans.tblTenantID
GROUP BY tenant.ID, tenant.Name;

drop table tblTenant;
drop table tblTransaction;