Difficulty in creating sum column in mysql

Question

Difficulty in creating sum column in mysql

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I am doing a commission report, but a question arose, I want to bring some totals at the end of the report, for example the gross value of all commissions, which would be vl_comis - desc_comis . Anyway, how do I create a column called total_valor_bruto that returns the sum of all the raw values of the commissions table?

SELECT 
a.id,
a.nf,
a.data_lcto,
a.data_prog,
a.data_pgto,
a.vl_comis,
a.desc_comis,
a.ir_comis, 
a.vl_pgto,
a.pedido_comis,
b.Empresa as emp,
b.RazaoSocial as razao,
c.cod, 
c.Representante as representante,
d.PeriodoID as periodo_comissao,
d.Periodo_inicial as inicial,
d.Periodo_final as final
FROM (((Comissoes a
INNER JOIN Empresas b ON a.EmpresaID = b.EmpresaID)
INNER JOIN Representantes c ON a.RepresentanteID = c.RepresentanteID)
INNER JOIN Periodos d ON a.PeriodoID = d.PeriodoID)
WHERE d.PeriodoID ='".$_COOKIE["cpr"]."' ORDER BY a.created");

mysql sql

asked by anonymous 20.09.2017 / 19:02

2 answers

How do I publish my application xamarin.forms Center text and change color JSP CSS

score 1 · Answer 1

First you have to put the calculation that you want done in the query (a.vl_comis - a.desc_comis) as total_valor_bruto with the function sum which is to sum all the results and put the group by to aggregate the results in the columns that do not have calculation.

SELECT 
  a.id,
  a.nf,
  a.data_lcto,
  a.data_prog,
  a.data_pgto,
  a.vl_comis,
  a.desc_comis,
  a.ir_comis, 
  a.vl_pgto,
  a.pedido_comis,
  b.Empresa as emp,
  b.RazaoSocial as razao,
  c.cod, 
  c.Representante as representante,
  d.PeriodoID as periodo_comissao,
  sum(a.vl_comis - a.desc_comis) as total_valor_bruto,
  d.Periodo_inicial as inicial,
  d.Periodo_final as final
FROM (((Comissoes a
INNER JOIN Empresas b ON a.EmpresaID = b.EmpresaID)
INNER JOIN Representantes c ON a.RepresentanteID = c.RepresentanteID)
INNER JOIN Periodos d ON a.PeriodoID = d.PeriodoID)
WHERE d.PeriodoID ='".$_COOKIE["cpr"]."'
GROUP BY
  a.id,
  a.nf,
  a.data_lcto,
  a.data_prog,
  a.data_pgto,
  a.vl_comis,
  a.desc_comis,
  a.ir_comis, 
  a.vl_pgto,
  a.pedido_comis,
  b.Empresa,
  b.RazaoSocial,
  c.cod, 
  c.Representante,
  d.PeriodoID,
  d.Periodo_inicial,
  d.Periodo_final
ORDER BY
  a.created;

score 0 · Answer 2

If I understand the question well just use the following query and change it for your use:

select sum(a.vl_comis + a.ir_comis) as total_valor_bruto

If this is not the answer, explain me a little better please?