Add open installments with Join

Question

Add open installments with Join

Navigation

#1 by (3 votes)

0

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

I need to add 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 client.

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

asked by anonymous 04.09.2018 / 15:19

1 answer

Using data from another site via LINK / ID Change color of the Sidemenu icon

score 3 · Accepted Answer

This should resolve, you were missing 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