Add open plots with Join

Asked

Viewed 28 times

0

I have two tables, Cliente and Parcela.
The table Parcela owns the id do cliente as foreign key, a column pagamento that is null or contains the payment date in format date and a column valor that is float and contains the amount paid or null open case.

I need to sum all paid installments and all open installments of all clients.

I’m trying to do this, but it’s adding up all the plots and showing only the first customer.

select c.nome_cliente, 
SUM(CASE WHEN p.pagamento is not null THEN p.valor ELSE 0 END) as 'PAGO',
SUM(CASE WHEN p.pagamento is null THEN p.valor ELSE 0 END) as 'ABERTO'
from cliente c
LEFT JOIN parcela p
ON c.id = p.id_cliente
mysql join

1 answer

3


That should solve, missed you inform the group by

select c.nome_cliente
      ,SUM(CASE WHEN p.pagamento is not null THEN p.valor ELSE 0 END) as 'PAGO'
      ,SUM(CASE WHEN p.pagamento is null THEN p.valor ELSE 0 END)     as 'ABERTO'
  from      cliente c
  left join parcela p ON p.id_cliente = c.id 
 group by c.nome_cliente

Browser other questions tagged mysql join

You are not signed in. Login or sign up in order to post.