Query on sql server with different value depending on some variables

Navigation
1

Good afternoon I have the following query: 

select distinct(T.Grupo) 'Grupo', MAX(G.Descricao) 'Grupo', sum(E.QtdCat*S.FactConvEst) 'M2 cativos'
from EncLin as E
INNER JOIN Stock as s
on s.CodProd=e.CodProd
inner join terceiros as t
on t.terceiro=e.Terceiro
inner join grupo as g
on g.Grupo=t.grupo  
where
E.QtdCat>0 and E.Estado in ('P','N','C') and E.Arm not in ('03P','05G') and E.TpDoc not in ('ENI','ENS')
Group by T.Grupo
order by 3 desc, 2

I needed to add two more columns, one that returns the value of sum (E.QtdCat S.FactConvEst) when E.State = 'N' and another that returns sum (E.QtdCat < in> S.FactConvEst) when E.State = 'C'.

I think this is done with case ... But I'm not sure why I tried it and it's giving wrong values.

    
asked by anonymous 07.11.2017 / 18:23

1 answer

0

With a CASE you can condition a sum 

select T.Grupo   Grupo , 
       MAX(G.Descricao) "Grupo", 
       sum(E.QtdCat*S.FactConvEst) "M2 cativos",
       sum(case when E.Estado='N' then E.QtdCatS.FactConvEst else 0 end) estado_n,
       sum(case when E.Estado='C' then E.QtdCatS.FactConvEst else 0 end) estado_c
from EncLin as E
INNER JOIN Stock as s
on s.CodProd=e.CodProd
inner join terceiros as t
on t.terceiro=e.Terceiro
inner join grupo as g
on g.Grupo=t.grupo  
where
E.QtdCat>0 and E.Estado in ('P','N','C') and 
E.Arm not in ('03P','05G') and 
E.TpDoc not in ('ENI','ENS')
Group by T.Grupo
order by 3 desc, 2
    
07.11.2017 / 19:23
URI Question 1463 in C, I can not identify the problem in the code reCaptcha in Delphi application