doubts with Mysql somatorio

Question

I have the following tables

the contamov table, may have several low of the same record of the crecpa table. need Select records from the crecpa table and the sum of the low (value field) of each record, tried with a subselect, but unsuccessfully, what is the correct way to do?

tried that way

SELECT con.id_crecpa,
    con.id_crecpa,
    con.descricao,
    con.diabase,
    con.dt_emissao,
    con.dt_quitacao,
    con.dt_vencto,
    con.id_plano_conta,
    con.nrparcela,
    con.obs,
    con.parcpai,
    con.qtdeparcela,
    con.saldo,
    con.status,
       con.tipo,
       REPLACE(con.valor, ',','.') AS valor,
       con.vencido,
       con.vlr_pago,
       pes.razao,
      pes.id_pessoa,
      pla.descricao,
      (SELECT SUM(a.valor) FROM contamov a INNER JOIN crecpa b ON b.id_crecpa = a.id_crecpa GROUP BY a.valor ) AS ddd,
       CASE 
       WHEN (con.dt_vencto) < CURDATE() THEN 'sim'
       WHEN (con.dt_vencto) > CURDATE() THEN 'nao'
       WHEN (con.dt_vencto) = CURDATE() THEN 'hoje'    
  END AS venc       
FROM crecpa con
INNER JOIN pessoa pes ON con.id_pessoa = pes.id_pessoa
INNER JOIN plano_conta pla ON con.id_plano_conta = pla.id_plano_conta   
WHERE con.id_empresa = 6
AND con.tipo = "Credito"
AND con.status = "Ativo"
AND con.dt_vencto BETWEEN '2018/01/01' AND '2019/05/10'
ORDER BY con.dt_vencto ASC

Answer 1

The question is a bit confusing, but if I understand correctly you want to add all the records contained in the table contamov correlating with the primary id of the table crecpa. In this case I believe that the subquerie below would suffice to sum all the values contained in the value attribute filtering for each id_crecpa that is being obtained from the main querie.

  SELECT ...  (SELECT SUM(valor) FROM contamov WHERE id_crecpa = 
  con.id_crecpa) 
  As valor FROM crecpa con WHERE con.id_empresa = 6 .... 

The detail is that this is a correlated subquerie, because we used the table alias in the case con to refer to principal darling and filter the data within our subquerie.

I didn’t really test the querie but the concept is this below:

SELECT con.id_crecpa,
       con.id_crecpa,
       con.descricao,
       con.diabase,
       con.dt_emissao,
       con.dt_quitacao,
       con.dt_vencto,
       con.id_plano_conta,
       con.nrparcela,
       con.obs,
       con.parcpai,
       con.qtdeparcela,
       con.saldo,
       con.status,
       con.tipo,
       REPLACE(con.valor, ',','.') AS valor,
       con.vencido,
       con.vlr_pago,
       pes.razao,
       pes.id_pessoa,
       pla.descricao,
       (SELECT SUM(valor) FROM contamov WHERE id_crecpa = con.id_crecpa) 
       As valor,
       CASE 
           WHEN (con.dt_vencto) < CURDATE() THEN 'sim'
           WHEN (con.dt_vencto) > CURDATE() THEN 'nao'
           WHEN (con.dt_vencto) = CURDATE() THEN 'hoje'    
      END AS venc       
FROM crecpa con
INNER JOIN pessoa pes ON con.id_pessoa = pes.id_pessoa
INNER JOIN plano_conta pla ON con.id_plano_conta = pla.id_plano_conta   
WHERE con.id_empresa = 6
AND con.tipo = "Credito"
AND con.status = "Ativo"
AND con.dt_vencto BETWEEN '2018/01/01' AND '2019/05/10'
ORDER BY con.dt_vencto ASC