How to do SUM() but with some rules to follow?

Question

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.

Answer 1

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

Answer 2

I heartily recommend McNets' answer. However, I thought I'd try to explain what the problem with your original query was.

That query again (in case it changes as you work through the question):

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

Your entire SELECT list (the columns to return) must proceed your FROM clause, and you can only have one FROM clause in a query.

You can break this into two queries:

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
;
select sum(vit_codigotiposexames) from
    (
    select * from tb_vitima where vit_codigotiposexames  = 21 )
    )Cadaverico

To turn this into a single query, I would use McNets' solution. However, there is a solution that looks more like what you were going for.

You can JOIN the two queries. Since each of the separate queries will return only one row, a CROSS JOIN (which matches each row in the first dataset to each row in the second [with a count of M rows in the first and N in the second, the results are M*N rows]) will work fine (1 * 1 = 1). First, we simplify the original two queries, and give the results the names you gave the two queries:

select sum(vit_codigotiposexames) as Corpo_Delito
  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 )
;
select sum(vit_codigotiposexames) as Cadaverico
  from tb_vitima
 where vit_codigotiposexames  = 21
;

Then, we turn the queries into subqueries, and join them:

select Corpo_Delito, Cadaverico
  from (select sum(vit_codigotiposexames) as Corpo_Delito
          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 )
       ) sq1
       CROSS JOIN -- This can just be a comma, but this make the intent clearer when you look at the code in the future.
       (select sum(vit_codigotiposexames) as Cadaverico
          from tb_vitima
         where vit_codigotiposexames  = 21
       ) sq2
;

Like I said, I would use McNets' solution, but hopeful this helps you understand why what you first tried didn't work as planned.

Answer 3

I realize you've already selected another answer, but I at least wanted to clean up my original offering.

select 
    (
    select sum(vit_codigotiposexames) as Corpo_Delito 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 )
    ) as Corpo_Delito,
    (
    select sum(vit_codigotiposexames) as Cadaverico from tb_vitima where vit_codigotiposexames  = 21 
    ) as Cadaverico