How to make a query using SQL correctly SUM

0

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 on the idea of bringing the total quantity sold of the product "JKit" for each month from April 2016, however I do not know why, it is repeating the month, rather than sum it all up. I think it could be wrong with SUM()

Result:

    
asked by anonymous 03.08.2017 / 20:53

2 answers

2

Friend, you ended up putting 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)
    
03.08.2017 / 21:12
2

If you want to group by month you need to select only the relative columns in 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 quantity ( ItQtde ) will group in line only when it is equal; that is, if you have different amounts in the sale of the same product, it will not add on the same line and will create one line for each. Ex: two sales of 10 products and two sales of 50; the select would have two lines, one with a total of 20 and one with a total of 100.

    
03.08.2017 / 21:03