I need to add values from a table

Question

I need to add values from a table

Navigation

#1 by (1 votes)

3

I have a SELECT and in it I display the sum of the sales values of each product, would I have to make a total sum of the table that made the sum?

SELECT 
     cadpro.descricao, 
     sum(itensped.total_venda) AS total_venda
FROM itensped
INNER JOIN pedido ON 
    itensped.id = pedido.id
NNER JOIN cadpro ON 
    itensped.id_cadpro = cadpro.id
INNER JOIN cadgrp ON 
    cadpro.id_grupo = cadgrp.id
       WHERE pedido.empresa = 1 
             AND pedido.filial = 1 
             AND pedido.data_venda BETWEEN '2017-05-01' AND '2017-05-31' 
             AND total_venda > 0
GROUP BY cadgrp.nome_grupo
ORDER BY  total_venda DESC LIMIT 10

Select result wanted to make a total sum of total_venda

mysql sql

asked by anonymous 08.12.2017 / 13:59

1 answer

Fix Function in Calculator How to make two Retrofit calls in jail with RxJava?

score 1 · Accepted Answer

Since you want the total sum of the 10 products with the highest sales value, you would suggest a sub-query. Example:

SELECT COALESCE(descricao, 'Somatório total de vendas'),
       SUM(total_venda) Total_Venda
  FROM 
(    
    SELECT 
         cadpro.descricao,
         sum(itensped.total_venda) AS total_venda
    FROM itensped
    INNER JOIN pedido ON 
        itensped.id = pedido.id
    INNER JOIN cadpro ON 
        itensped.id_cadpro = cadpro.id
    INNER JOIN cadgrp ON 
        cadpro.id_grupo = cadgrp.id
           WHERE pedido.empresa = 1 
                 AND pedido.filial = 1 
                 AND pedido.data_venda BETWEEN '2017-05-01' AND '2017-05-31' 
                 AND total_venda > 0
    GROUP BY cadpro.descricao
    ORDER BY  total_venda DESC LIMIT 10
) Res
GROUP BY descricao WITH ROLLUP

This will result in a result set with 11 records. The 10 products with the highest sales value and a record with the total sum for these 10 products.

If the goal is just to return a single record with the total sum of total_bundle you can do:

SELECT 'Somatório total de vendas',
       SUM(total_venda) Total_Venda
  FROM 
(    
    SELECT 
         cadpro.descricao,
         sum(itensped.total_venda) AS total_venda
    FROM itensped
    INNER JOIN pedido ON 
        itensped.id = pedido.id
    INNER JOIN cadpro ON 
        itensped.id_cadpro = cadpro.id
    INNER JOIN cadgrp ON 
        cadpro.id_grupo = cadgrp.id
           WHERE pedido.empresa = 1 
                 AND pedido.filial = 1 
                 AND pedido.data_venda BETWEEN '2017-05-01' AND '2017-05-31' 
                 AND total_venda > 0
    GROUP BY cadpro.descricao
    ORDER BY  total_venda DESC LIMIT 10
) Res