I need to add values from a table

Question

I own a SELECT and in it I display the sum of the sales values of each product, I would like 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

Answer 1

Since you want the total sum of the 10 products with the highest selling value, I 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 set of results with 11 records. The 10 products with the highest selling value and a record with the total sum for those 10 products.

If the goal is only to return a single record with the total sum of total_sale 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