How can I solve this equation in SQL? X = (A - (B+C+D+E))

Question

I have a table with the following columns:

COD_PRODUTO|PRODUTO|UNIDADE|FAMILIA|ORIGEM|ESTOQUE_ATUAL|COMPRAS_ENTRADAS|CONSUMO_PRODUCAO|ENTRADA_PRODUCAO|ESTOQUE_MIN|CMC|DIFERENÇA|PREÇO_REPOS

I need to do these operations by grouping by COD_PRODUTO.

DIFERENCA = ESTOQUE_MIN-(ESTOQUE_ATUAL+COMPRAS_ENTRADAS+CONSUMO_PRODUCAO+ENTRADA_PRODUCAO)

PREÇO_REPOS = DIFERENCA*CMC

I tried that way:

DECLARE @DIF INT, @PRECO MONEY
SET @DIF = (SELECT (ESTOQUE_MIN-(ESTOQUE_ATUAL+COMPRAS_ENTRADAS+CONSUMO_PRODUCAO+ENTRADA_PRODUCAO)) FROM #ESTOQUE_DIFERENCA)
SET @PRECO = (SELECT (@DIF*CMC) FROM #ESTOQUE_DIFERENCA)

INSERT INTO #ESTOQUE_DIFERENCA
    SELECT COD_PRODUTO, FAMILIA, ESTOQUE_MIN 
    ,SUM(ESTOQUE_ATUAL) 
    ,SUM(COMPRAS_ENTRADAS) 
    ,SUM(CONSUMO_PRODUCAO) 
    ,SUM(ENTRADA_PRODUCAO) 
    , @DIF 
    , CMC
    , sum(PREÇO_REPOS)
        FROM ESTOQUE_INTEGRADO
        GROUP BY COD_PRODUTO,CMC, FAMILIA, ESTOQUE_MIN

but returns this error:

The sub-allowance returned more than 1 value. This is not allowed when the subconsulta follows a =, != , <, <= , >, >= or when it is used as an expression.

I’m new to SQL, and I couldn’t find another way to do it, someone can help me?

Answer 1

I see that this error message must be being generated by these lines

SET @DIF = (SELECT (ESTOQUE_MIN-(ESTOQUE_ATUAL+COMPRAS_ENTRADAS+CONSUMO_PRODUCAO+ENTRADA_PRODUCAO)) FROM #ESTOQUE_DIFERENCA)
SET @PRECO = (SELECT (@DIF*CMC) FROM #ESTOQUE_DIFERENCA)

You would need to use a grouping function (SUM) so that the return was only 1 record, so it will return "n" records, so the error message.

One of the ways to get this result you are looking for would be to insert the "@DIF" into the main query, and then, based on this dataset, make a second query to be able to generate @PRECO. From what I can see, you use this query to create a temporary table called #ESTOQUE_DIFERENCA.

Your query creating this table would look like this:

select *, DIFERENCA*CMC as preco from (
SELECT COD_PRODUTO, FAMILIA, ESTOQUE_MIN 
,SUM(ESTOQUE_ATUAL) as estoque_atual,
,SUM(COMPRAS_ENTRADAS) as compras_entradas,
,SUM(CONSUMO_PRODUCAO) as consumo_producao, 
,SUM(ENTRADA_PRODUCAO) as entrada_producao
,SUM(ESTOQUE_MIN-(ESTOQUE_ATUAL+COMPRAS_ENTRADAS+CONSUMO_PRODUCAO+ENTRADA_PRODUCAO)) as DIFERENCA
,CMC
,sum(PREÇO_REPOS) as preco_repos
FROM ESTOQUE_INTEGRADO
GROUP BY COD_PRODUTO,CMC, FAMILIA, ESTOQUE_MIN
) tabela