Sum values of the same code

Asked

Viewed 105 times

-2

Hello, I have a table that lists purchase quantity of each product, for example, if the buyer makes 3 purchase orders he repeats the orders and quantities. An example of this is the image below:

inserir a descrição da imagem aqui

for example, the soda that has 3 orders of 900, I would like to add this to appear 2700 understand? Would you be able to do this in the select of mysql? Or by PHP?

I would prefer it to be by mysql, to give maintenance later facilitates me. But in PHP I also accept.

My select:

$stid = "SELECT b.id, b.codigo, a.codacesso, a.seqproduto, a.desccompleta, b.quantidade, b.data_vencimento, b.data_atual, b.observacao, b.usuario, b.estado, b.loja, a.medvdiageral, a.comprador, a.preco
FROM master_datas_b a, master_coletores b 
WHERE b.tipo_acao IS NULL and a.comprador = :comprador and a.nroempresa = :loja and b.loja = :loja and estado = 'Ativo' and b.codigo = a.codacesso AND b.data_vencimento BETWEEN TO_DATE(:data1,'YYYY-MM-DD') AND TO_DATE(:data2,'YYYY-MM-DD') and b.quantidade > 0 ORDER BY b.data_vencimento, b.codigo ASC";
php mysql

  • I believe that the best way is to use the SUM() function with GROUP BY. That is, group your information by request and use the SUM(value) in the select of your query. Maybe it helps: Link I hope I’ve helped!

  • 2

    You have to make one GROUP BY for EAN and apply a SUM in Quantidade. To form an exact answer, it would be interesting if you put your SELECT and its structure

  • put my sql that makes the listing

  • The field containing the EAN is the codigo or codacesso?

  • is the seqproduto guy

  • ta giving error :T

  • What mistake you’re making?

Show 2 more comments

2 answers

1

This would be a grouping of records as well as others SGBD Mysql has a clause for just that, which is the GROUP BY. With it you can group the data by column seqproduto, would look something like this:

SELECT
    b.id, b.codigo, a.codacesso, a.seqproduto, a.desccompleta, SUM(b.quantidade) AS quantidade,
    b.data_vencimento, b.data_atual, b.observacao, b.usuario, b.estado, b.loja, a.medvdiageral,
    a.comprador, a.preco
FROM master_datas_b a, master_coletores b 
WHERE
    b.tipo_acao IS NULL and a.comprador = :comprador and a.nroempresa = :loja
    and b.loja = :loja and estado = 'Ativo' and b.codigo = a.codacesso
    AND b.data_vencimento BETWEEN TO_DATE(:data1,'YYYY-MM-DD') AND TO_DATE(:data2,'YYYY-MM-DD')
    and b.quantidade > 0
GROUP BY seqproduto
ORDER BY b.data_vencimento, b.codigo ASC

See more about GROUP BY here.

0

Got it this way, because they are negativing my question?

SELECT a.seqproduto, a.desccompleta, SUM(b.quantidade) AS QUANTIDADE
FROM master_datas_b a, master_coletores b 
WHERE b.tipo_acao IS NULL and a.comprador = :comprador and a.nroempresa = :loja and b.loja = :loja and estado = 'Ativo' and b.codigo = a.codacesso AND b.data_vencimento BETWEEN TO_DATE(:data1,'YYYY-MM-DD') AND TO_DATE(:data2,'YYYY-MM-DD') and b.quantidade > 0 GROUP BY seqproduto, desccompleta ORDER BY a.seqproduto, a.desccompleta ASC

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.