Sql-server – How to do SUM() but with some rules to follow

sql serversql server 2014

Maybe this question is confusing to understand ( as I'm not fluent in English ) but,
I need to select sum(cod) for example, but this sum() needs to have the following rule:

Part 1:
it needs to be ALL cods, except for 21.

Part2:
Another column, with another sum(), but only with cod 21.

it's not the entire query, But after this, I think I can do it.

I tried this:

select sum(vit_codigotiposexames) from 
    (
    select * from tb_vitima where vit_codigotiposexames  
        in (2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,22 )
    )Corpo_Delito,
sum(vit_codigotiposexames) from
    (
    select * from tb_vitima where vit_codigotiposexames  = 21 )
    )Cadaverico

It works If I have only

select sum(vit_codigotiposexames) from 
    (
    select * from tb_vitima where vit_codigotiposexames  
        in (2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,22 )
    )Corpo_Delito

is there a way to make this query, a unique query? and, is there a way to name these columns? because I know I need to alias them, but they stay with that (no column name) above it.

Best Answer

I think you can differentiate this SUM's using a SUM(CASE) statement:

select sum(case when vit_codigotiposexames = 21 
                then vit_codigotiposexames 
                else 0 end) Cadaverico,
       sum(case when vit_codigotiposexames <> 21 
                then vit_codigotiposexames 
                else 0 end) CorpoDelito
from   tb_vitima;

I've set up an example:

create table tb_vitima (id int identity, vit_codigotiposexames int);
insert into tb_vitima values (2),(5),(10),(11),(21),(21),(10),(23);

select sum(case when vit_codigotiposexames = 21 then vit_codigotiposexames else 0 end) Cadaverico,
       sum(case when vit_codigotiposexames <> 21 then vit_codigotiposexames else 0 end) CorpoDelito
from   tb_vitima;
GO
Cadaverico | CorpoDelito
---------: | ----------:
        42 |          61

dbfiddle here