Query in sql server with different value depending on some variables

Question

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.QtdcatS.Factconvest) when E.State='N' and another that returns the value of sum(E.QtdcatS.Factconvest) when E.State='C'.

I think this is done with case... But I’m not sure until because I’ve tried and is giving wrong values.

Answer 1

With a CASE a sum can be conditioned

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