HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlModerate

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

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
howwithbutsomefollowsumrules

Problem

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.

Solution

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

Code Snippets

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;
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

Context

StackExchange Database Administrators Q#168518, answer score: 14

Revisions (0)

No revisions yet.