HOW TO ADD UP THE VALUE OF A PRO CLIENT COLUMN

Asked

Viewed 44 times

0

Good afternoon Gentlemen, In benefit of my studies I am developing a simple sales system and I am already in the part of the reports.

My doubt is as follows: How to make a select adding up the amount per customer. Next, on the same day a customer can buy several times the same product, I would like to know how to display only once that customer with the total purchase quantity that day.

At the moment my select is like this: 

select id_venda as "Cod Venda", pro_nome as "Produto", ven_pro_quantidade as "Quantidade",
 nome_cli as "Cliene", ven_dataVenda as "Data" from tblvendas
INNER JOIN tblclientes
ON tblvendas.fk_cliente = tblclientes.idCliente
INNER JOIN tblvendas_produtos
ON tblvendas.id_venda = tblvendas_produtos.fk_id_venda
INNER JOIN tblproduto
ON tblvendas_produtos.fk_id_produto = tblproduto.id_produto

Giving me the following result :

inserir a descrição da imagem aqui

For a better explanation, let’s take the JUICE OF PEAR that made several purchases on the day 11/25/19, need to display the JUICE OF PEAR only once adding up the amount that he bought the day. (PEAR JUICE and other Customers who are in the same condition)

java mysql query select-sql report
  • 1

    Use the clause GROUP BY cliente, produto, data and on the list of SELECT use the aggregation function SUM(quantidade). Note that the column will not have much sense Cod Venda.

  • @Mark Alexander: But is that not what the author of the question asked? See the last paragraph of the question. It is true that the client name used as an example was not a good choice.

  • Did any of the answers solve your problem?

2 answers

1

select pro_nome
    , sum(ven_pro_quantidade)
    , ven_dataVenda
from
    tblvendas as a
    INNER JOIN tblclientes as b ON a.fk_cliente = b.idCliente
    INNER JOIN tblvendas_produtos as c ON a.id_venda = c.fk_id_venda
    INNER JOIN tblproduto as d ON c.fk_id_produto = d.id_produto
GROUP BY pro_nome, ven_dataVenda

0

This should help: for the quantity we do the sum (sum), and the other fields are grouped, in case it can be by the name of the field or by the order they appear in the selection list:

select
    id_venda as "Cod Venda", 
    pro_nome as "Produto", 
    sum(ven_pro_quantidade) as "Quantidade",
    nome_cli as "Cliene", 
    ven_dataVenda as "Data" 
from
    tblvendas
    INNER JOIN tblclientes ON tblvendas.fk_cliente = tblclientes.idCliente
    INNER JOIN tblvendas_produtos ON tblvendas.id_venda = tblvendas_produtos.fk_id_venda
    INNER JOIN tblproduto ON tblvendas_produtos.fk_id_produto =         tblproduto.id_produto
GROUP BY 1, 2, 4, 5

Browser other questions tagged java mysql query select-sql report

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