Summation in SQL

Question

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

In the general report, I need it to stay that way:

Produto       Quantidade Total     Valor total

Salpicão      30kgs                810,00

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

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

I started to do so but it is not right, follow the bank diagram:

Answer 1

What you need is to make a data curve with the table itens_pedido using JOIN in case I used the INNER JOIN to pick up only products that have orders. But then you can research 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 table of itens_pedido

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;