How to make a query using SUM correctly SQL

Question

I have the following query:

 SELECT
  YEAR(NFDtEmis) AS 'Ano',
  MONTH(NFDtEmis) AS 'Mes',
  ItProduto AS 'Produto',
  SUM(ItQtde) AS 'Quantidade total'
FROM NotasItens
INNER JOIN NotasFiscais
  ON NFEmpresa = ItEmpresa
  AND NFNumero = ItNfNumero
  AND NFTIPO = ItTipo
INNER JOIN Tributos
  ON TribEmpresa = ItEmpresa
  AND TribCodigo = ItCFO
WHERE ItEmpresa = '03'
AND ItProduto = 'JKIT'
AND NFDtEmis >= '2016-04-01'
AND NFDtEmis <= '2017-07-31'
AND NFStatus = 'I'
AND NFTipo = 'S'
AND NFEmpresa = '03'
AND UPPER(RTRIM(LTRIM(TribTpCFOP))) = 'VENDA'
GROUP BY YEAR(NFDtEmis),
         MONTH(NFDtEmis),
         ItProduto,
         ItQtde
ORDER BY YEAR(NFDtEmis), MONTH(NFDtEmis)

I wrote in the idea of bringing the total sold quantity of the product "Jkit" for each month from April/2016, but I do not know why, he is repeating the month, instead of adding everything up. I believe I may be wrong SUM()

Upshot:

Answer 1

If you want to group by month you need to select only the columns group by, without grouping by quantity:

SELECT
  YEAR(NFDtEmis) AS 'Ano',
  MONTH(NFDtEmis) AS 'Mes',
  ItProduto AS 'Produto',
  SUM(ItQtde) AS 'Quantidade total'
FROM NotasItens
INNER JOIN NotasFiscais
  ON NFEmpresa = ItEmpresa
  AND NFNumero = ItNfNumero
  AND NFTIPO = ItTipo
INNER JOIN Tributos
  ON TribEmpresa = ItEmpresa
  AND TribCodigo = ItCFO
WHERE ItEmpresa = '03'
AND ItProduto = 'JKIT'
AND NFDtEmis >= '2016-04-01'
AND NFDtEmis <= '2017-07-31'
AND NFStatus = 'I'
AND NFTipo = 'S'
AND NFEmpresa = '03'
AND UPPER(RTRIM(LTRIM(TribTpCFOP))) = 'VENDA'
GROUP BY YEAR(NFDtEmis),
         MONTH(NFDtEmis),
         ItProduto
ORDER BY YEAR(NFDtEmis), MONTH(NFDtEmis)

detail: grouping by the quantity (ItQtde) will group in the row only when it is equal; that is, if you have different quantities in the sale of the same product, it will not add in the same row and create a row for each. Ex: two sales of 10 products and two sales of 50; the select would bring two lines, one with total 20 and the other with total 100.

Answer 2

Buddy, you just put in the amount ( ItQtde ) in group by.

SELECT
  YEAR(NFDtEmis) AS 'Ano',
  MONTH(NFDtEmis) AS 'Mes',
  ItProduto AS 'Produto',
  SUM(ItQtde) AS 'Quantidade total'
FROM NotasItens
INNER JOIN NotasFiscais
  ON NFEmpresa = ItEmpresa
  AND NFNumero = ItNfNumero
  AND NFTIPO = ItTipo
INNER JOIN Tributos
  ON TribEmpresa = ItEmpresa
  AND TribCodigo = ItCFO
WHERE ItEmpresa = '03'
AND ItProduto = 'JKIT'
AND NFDtEmis >= '2016-04-01'
AND NFDtEmis <= '2017-07-31'
AND NFStatus = 'I'
AND NFTipo = 'S'
AND NFEmpresa = '03'
AND UPPER(RTRIM(LTRIM(TribTpCFOP))) = 'VENDA'
GROUP BY YEAR(NFDtEmis),
         MONTH(NFDtEmis),
         ItProduto,
--       ItQtde /* invalido no group by */
ORDER BY YEAR(NFDtEmis), MONTH(NFDtEmis)