doubts with summation Mysql

Question

doubts with summation Mysql

Navigation

#1 by (2 votes)

0

I have the following tables

Thetablecontamov,canhaveseveralcasualtiesofthesamerecordofthetablecrecpa.Ineedtoselectrecordsfromthecrecpatableandthesumofthelows(valuefield)ofeachrecord,Itriedwithasubselect,butwithoutsuccess,whatistherightwaytodoit?

Itriedthisway

SELECTcon.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,',','.')ASvalor,con.vencido,con.vlr_pago,pes.razao,pes.id_pessoa,pla.descricao,(SELECTSUM(a.valor)FROMcontamovaINNERJOINcrecpabONb.id_crecpa=a.id_crecpaGROUPBYa.valor)ASddd,CASEWHEN(con.dt_vencto)<CURDATE()THEN'sim'WHEN(con.dt_vencto)>CURDATE()THEN'nao'WHEN(con.dt_vencto)=CURDATE()THEN'hoje'ENDASvencFROMcrecpaconINNERJOINpessoapesONcon.id_pessoa=pes.id_pessoaINNERJOINplano_contaplaONcon.id_plano_conta=pla.id_plano_contaWHEREcon.id_empresa=6ANDcon.tipo="Credito"
AND con.status = "Ativo"
AND con.dt_vencto BETWEEN '2018/01/01' AND '2019/05/10'
ORDER BY con.dt_vencto ASC

mysql select

asked by anonymous 13.01.2018 / 23:00

1 answer

What is the difference between the query with '(quotation marks) and without' (quotation marks)? Append not working properly on a Select after AJAX request

score 2 · Accepted Answer

The question is a bit confusing, but if I understand correctly you want to add all records contained in the contamov table by correlating with the primary id of the crecpa table. In the case I believe it would suffice the subquery below to sum all the values contained in the value attribute by 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 subquery because we use the table alias in case to reference the main query and filter the data within our subquery.

I have not tried 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