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

(你没有陈述你的sql引擎,所以我将链接到MySQL文档)。

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:

这几乎就是COALESCE()函数的用途。您可以将其提供给列表,它将返回列表中的第一个非空值。您可以在查询中使用它,如下所示:

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.

这样,如果SUM()结果为NULL,则将其替换为零。

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.

编辑:我使用LEFT JOIN和COALESCE()重写了查询,我认为这是你最初缺少的关键。如果仅从“事务”表中进行选择,则无法获取有关表中不包含的内容的信息。但是,通过使用Tenants表中的左连接,您应该为每个现有租户获取一行。

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.

以下是该问题的完整演练。还包括函数isnull,以确保为没有事务的租户返回零(而不是null)的余额。

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;

Select Tenants.ID, ISNULL((SUM(tblTransaction.AmountPaid) - SUM(tblTransaction.AmountCharged)), 0) AS TenantBalance
From Tenants 
Left Outer Join Transactions Tenants.ID = Transactions.TenantID
Group By Tenents.ID

I didn't syntax check it but it is close enough.

我没有语法检查它,但它足够接近。

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

I only added this because if you're intention is to take into account for one of the parts being null you'll need to do the ISNULL separately

我只是添加了这个,因为如果你打算考虑其中一个零件是空的,你需要单独做ISNULL

Actually, I found an answer:

实际上,我找到了答案:

SELECT tenant.ID, ISNULL(SUM(trans.AmountPaid) - SUM(trans.AmountCharged),0) AS Balance FROM tblTenant tenant
LEFT JOIN tblTransaction trans
ON tenant.ID = trans.TenantID
GROUP BY tenant.ID