Add result of another sum

Question

Add result of another sum

Navigation

#1 by (0 votes)

2

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

    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 query:

How do I turn these two results into one?

Note: I already tried to add SUM but it did not work.

sql sql-server

asked by anonymous 19.09.2018 / 15:24

1 answer

Motor PBSS | Add Line How to make two WHERE clauses inside a nested WITH

score 0 · Answer 1

As stated in the comments, you should remove group by ; the results presented will be brought in only one row, 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