Add another sum result

Asked

Viewed 97 times

2

I’m trying to make a sum of the result of another operation. Below is the code I’m trying to

    select
    distinct

    (SELECT(SUM(CAST(ROUND(ppre.Valor_Custo, 2) as decimal(18,2))) )) * 

    (select  isnull(sum(case when e.Quantidade < 0 then 0 else e.Quantidade 
    end),0)from Estoque e 
    inner join deposito d on d.ID = e.ID_Deposito and d.ID_Empresa in (1,2) 
    and 
    d.Ativo = 1 
    where  e.ID_Produto = prod.ID)


    as 'Custo total'
    FROM Produto prod 
    inner join Produto_Empresa ppre on ppre.ID_Produto = prod.ID

    group by prod.id

This is the result of the consultation:

inserir a descrição da imagem aqui

How do I turn these two results into one?

Note: I tried to add one SUM but it didn’t work out.

sql database sql-server
  • 3

    Takes the group by

  • 2

    as @Sorack said, is grouping, if taking the group will bring a sum only, or uses a CTE: with dados as ( ...aqui vai seu select ...) select sum('Custo total') from dados

1 answer

0

As stated in the comments, you should remove the group by; the results presented will be brought in only one line, since there is no division/grouping by id:

SELECT DISTINCT 
    (SELECT(SUM(CAST(ROUND(ppre.Valor_Custo, 2) as decimal(18,2))) )) * 
    (select  isnull(sum(case when e.Quantidade < 0 then 0 else e.Quantidade end),0)
     FROM Estoque e 
     INNER JOIN deposito d ON d.ID = e.ID_Deposito AND d.ID_Empresa in (1,2) AND d.Ativo = 1 
     WHERE e.ID_Produto = prod.ID) AS 'Custo total'
FROM Produto prod 
INNER JOIN Produto_Empresa ppre on ppre.ID_Produto = prod.ID

Browser other questions tagged sql database sql-server

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