SUM values in SQL?

I have this query.

SELECT  NFI.ID_Produto, SUM(NFI.Quantidade) AS Quantidade, MIN(PS.Descricao) AS Nome, MIN(NFI.ValorUnitario) AS Valor,
    SUM(NFI.ValorTotal) AS ValorTotal
FROM NotaFiscal_Item NFI
INNER JOIN Produto_Servico PS ON NFI.ID_Produto = PS.ID
INNER JOIN NotaFiscal NF ON NFI.ID_NF = NF.ID
INNER JOIN Venda V ON NF.ID_Venda = V.ID
INNER JOIN Usuario U ON V.ID_UsuarioComissao1 = U.ID
WHERE U.Descricao = 'MANHA' AND (NF.DataEmissao >= '2019-04-05 12:00:00' AND NF.DataSaida <= '2019-04-05 23:59:59')
GROUP BY NFI.ID_Produto
ORDER BY ValorTotal DESC

And this is the result.

enter image description here

How do I sum the values of the column ValorTotal to show on the result?

1 answer

  • answered 2020-04-05 21:46 GMB

    Presumably, you want a window sum:

    SELECT  
        NFI.ID_Produto, 
        SUM(NFI.Quantidade) AS Quantidade, 
        MIN(PS.Descricao) AS Nome, 
        MIN(NFI.ValorUnitario) AS Valor,
        SUM(NFI.ValorTotal) AS ValorTotal,
        SUM(SUM(NFI.ValorTotal)) OVER() AS ValorOverall
    FROM ...
    GROUP BY NFI.ID_Produto
    ORDER BY ValorTotal DESC
    

    This adds one more column to the resultset, with the grand total of ValorTotal (ie the sum of ValorTotal over all rows in the resultset).