SQL Summation

Question

SQL Summation

Navigation

#1 by (0 votes)

1

I need to make a report with SQL , which asks me the following situation.

In the general report, I need it to look like this:

Produto       Quantidade Total     Valor total

Salpicão      30kgs                810,00

The detail is, it is necessary that the reports that contain the item "Total quantity" demand the total quantity of the product taking into account all the orders made.

SELECT   produto.nome, produto.tipo_medicao, 
 (select count(produto.valor) FROM produto) as total  
   FROM produto LIMIT 0, 1000

I started doing this but it is not right, follow the bank diagram:

mysql sql

asked by anonymous 19.12.2017 / 13:59

1 answer

Which method to use and how? onLoadResourse or webResourceResponse ShouldInterceptRequest How to change the field of a table, taking values from another table as a reference. Using cakephp 1.3

score 0 · Accepted Answer

What you need is to do a data curl with the itens_pedido table using JOIN in case I used INNER JOIN to pick only the products that have orders. But here you can search better and see what meets your need, in this query I am also returning the sum of the product in its original value and the value it has in the itens_pedido table

SELECT
    p.nome,
    SUM(IFNULL(ip.quantidade,0)) AS quantidade_total,
    SUM(IFNULL(ip.valor_total,0)) AS valor_total_pedido,
    SUM(p.valor) AS valor_produto
FROM produto p
INNER JOIN itens_pedido ip
ON ip.produto_id_produto = p.id_produto
GROUP BY p.id_produto;