Adding Amount of Input per month and customer

Asked

Viewed 22 times

1

Hi, I can’t add up the amount of incoming products per customer per month. Can anyone help me with any tips? I’m getting the following result:

MÊS Quantidade
1   95
1   55
2   90
3   140
3   40
3   95
3   40

Correct would be:

MÊS Quantidade
1   150
2   90
3   140
3   315

Query:

SELECT MONTH(TMOV.DATASAIDA) AS MES, SUM(TITMMOV.QUANTIDADE) AS QUANTIDADE
FROM 
(TPRD INNER JOIN (FCFO INNER JOIN (TMOV INNER JOIN TITMMOV ON (TMOV.IDMOV = TITMMOV.IDMOV) 
AND (TMOV.CODCOLIGADA = TITMMOV.CODCOLIGADA)) ON FCFO.CODCFO = TMOV.CODCFO) ON (TPRD.IDPRD = TITMMOV.IDPRD) 
AND (TPRD.CODCOLIGADA = TMOV.CODCOLIGADA)) INNER JOIN TMOVCOMPL ON (TMOV.IDMOV = TMOVCOMPL.IDMOV) 
AND (TMOV.CODCOLIGADA = TMOVCOMPL.CODCOLIGADA)
WHERE TITMMOV.CODCOLIGADA Between '1' And '2' AND TMOV.CODTMV='1.1.70'
AND TMOV.CODCFO ='003582' AND CIDADECOLETA = '00051'
AND TMOV.DATASAIDA >= '2021-01-01 00:00:00'
AND TPRD.CODIGOPRD >= '04.001.0001' AND TPRD.CODIGOPRD <= '04.003.9999' 
And TPRD.CODIGOPRD > '03.999.9999' AND TMOV.STATUS <> 'C'
GROUP BY TITMMOV.QUANTIDADE, TMOV.DATASAIDA
sql sql-server sum

  • must be grouped by the same criterion as the select: group by MONTH(TMOV.DATASAIDA), quantity is already grouped in the SUM, don’t need to be in group by

1 answer

0

Late, the error is in Group BY, change to GROUP BY MONTH(TMOV.DATAOUTPUT)

SELECT MONTH(TMOV.DATASAIDA) AS MES, SUM(TITMMOV.QUANTIDADE) AS QUANTIDADE
FROM DBO.TMOV 
INNER JOIN DBO.TITMMOV ON (TMOV.CODCOLIGADA = TITMMOV.CODCOLIGADA AND TMOV.IDMOV = TITMMOV.IDMOV)
INNER JOIN DBO.TPRODUTO ON (TPRODUTO.CODCOLPRD = TITMMOV.CODCOLIGADA AND TPRODUTO.IDPRD = TITMMOV.IDPRD) 
WHERE TMOV.CODCOLIGADA Between '1' And '2' AND TMOV.CODTMV='1.1.70'
AND TMOV.CODCFO ='003582' 
--> QUAL TABELA 
AND CIDADECOLETA = '00051'
AND TMOV.DATASAIDA >= '2021-01-01 00:00:00'
AND TPRODUTO.CODIGOPRD >= '04.001.0001' 
AND TPRODUTO.CODIGOPRD <= '04.003.9999' 
And TPRODUTO.CODIGOPRD > '03.999.9999' AND TMOV.STATUS <> 'C'
GROUP BY  MONTH(TMOV.DATASAIDA)

however, your query is doing JOIN in unused tables, so for better performance you should check!

Browser other questions tagged sql sql-server sum

You are not signed in. Login or sign up in order to post.