Como posso obter essa consulta para retornar 0 em vez de null?

votos
4

Eu tenho esta consulta:

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

Mas há um problema com ele; há outras TenantID de que não possuem transações e eu quero começar os demais.

Por exemplo, a tabela de transação tem 3 linhas de prumo, 2 linha para john e nenhum para jane. Eu quero que ele retornar a soma de bob, João e retornar 0 para jane. (Ou possivelmente nulo se não há nenhuma outra maneira)

Como posso fazer isso?

As tabelas são assim:

inquilinos  
  identidade  
  Outros dados  
transações  
  identidade  
  TenantID (fk para inquilinos)
  Outros dados  
Publicado 19/05/2009 em 18:45
fonte usuário
Em outras línguas...                            


5 respostas

votos
14

(Você não declarou o seu motor de sql, então eu vou fazer o link com a documentação MySQL).

Isso é muito bonito exatamente o que a COALESCE()função se destina. Você pode alimentá-lo uma lista, e ele vai retornar o primeiro valor não nulo na lista. Você poderia usar isso em sua consulta da seguinte maneira:

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;

Dessa forma, se o SUM()resultado seria NULL, ele é substituído com zero.

Editado : eu reescrevi a consulta usando um LEFT JOIN, bem como o COALESCE(), eu acho que esta é a chave do que você estava faltando originalmente. Se você selecionar apenas da tabela de transações, não há nenhuma maneira de obter informações sobre as coisas não na tabela. No entanto, usando uma associação à esquerda da tabela de inquilinos, você deve obter uma linha para cada inquilino existente.

Respondeu 19/05/2009 em 18:48
fonte usuário

votos
1

Abaixo está um passo a passo completo do problema. O isnull função também foi incluído para garantir que um saldo de zero (em vez de null) é retornado para inquilinos com nenhuma transação.

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;
Respondeu 19/05/2009 em 19:25
fonte usuário

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

Eu só adicionado este porque se você for intenção é levar em conta para uma das partes, sendo nula você vai precisar para fazer o ISNULL separadamente

Respondeu 19/05/2009 em 18:53
fonte usuário

votos
0
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

Eu não sintaxe verificá-lo, mas é perto o suficiente.

Respondeu 19/05/2009 em 18:50
fonte usuário

votos
-1

Na verdade, eu encontrei uma resposta:

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
Respondeu 19/05/2009 em 18:52
fonte usuário

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more