sum values in subquery by field

Question

Good morning, I’m trying to solve this but I can’t, in this select he has a field called emprestimo_id. I need to sum the remaining amount of each loan into one subquery calling for "remainder", but what happens is that it sums up the value of all loans. I need it to show only the sum of each loan number.

SELECT 
    id,
    valor,
    data_pagamento,
    (SELECT nome_fantasia FROM clientes WHERE id = cliente_id) as cliente,
    (SELECT observacoes FROM clientes WHERE id = cliente_id) as observacoes,
    (SELECT CONCAT(endereco, ' N: ',numero ) FROM clientes WHERE id = cliente_id) as endereco,
    data_recebimento,
    prazo,
    cliente_id,
    emprestimo_id,
    valor_pago,
    forma_pagamento,
    (SELECT (SELECT parcelas * valor_parcela FROM emprestimo WHERE id = emprestimo_id) - SUM(valor_pago) as saldo_devedor FROM parcelas WHERE emprestimo_id = emprestimo_id AND juros != 'sim' ) as restante
FROM 
    parcelas
WHERE data_pagamento BETWEEN 20210505 AND 20210505 AND emprestimo_id NOT IN (SELECT renegociacao FROM emprestimo WHERE cliente_id = cliente_id) AND emprestimo_id IN (SELECT id FROM emprestimo WHERE quitado != 'sim' AND cliente_id IN (SELECT id FROM clientes WHERE forma_pagamento = 'DINHEIRO' AND usuario_cobranca = 'FULANO'))

Answer 1

I need to sum the remaining amount of each loan in a subquery called "remainder", but what happens is that it adds up the value of all loans. I need you to show only the sum of each number loan.

Let’s isolate the sub-volume called "remaining", formatting it for easy reading.

(         
  select (select parcelas * valor_parcela
            from emprestimo
           where id = emprestimo_id) - SUM(valor_pago) as saldo_devedor
    from parcelas          
   where emprestimo_id = emprestimo_id
     and juros != 'sim'   
) as restante

The problem is that in where is emprestimo_id = emprestimo_id. It is necessary to reference the emprestimo_id of the main consultation.

Solution

I suggest that in the FROM parcelas create an aliasse by getting FROM parcelas p and then on the sub-base you will reference the p being as follows:

(
    select (select parcelas * valor_parcela
              from emprestimo
             where id = emprestimo_id) - SUM(valor_pago) as saldo_devedor
      from parcelas
     where emprestimo_id = p.emprestimo_id
       and juros != 'sim' 
) as restante

In this way, the sub-allowance must return the amount for each loan.

Another point of attention:

  AND emprestimo_id NOT IN (SELECT renegociacao 
                              FROM emprestimo 
                             WHERE cliente_id = cliente_id) 

I believe that here also should reference the main query p:

  AND emprestimo_id NOT IN (SELECT renegociacao 
                              FROM emprestimo 
                             WHERE cliente_id = p.cliente_id) 

As a rule, whether the sub-allowance should make a specific calculation of a record of the consulta principal which is being "processed" by the database, this sub-query must reference a field of the main query. When you don’t do it that way, it’s like in your where did not result in any difference in the SQL command, as it is cliente_id = cliente_id were 1 = 1 or 2 = 2 etc, because the field is referring to itself and not to a variable external.