snippetsqlModerate
How to do SUM() but with some rules to follow?
Viewed 0 times
howwithbutsomefollowsumrules
Problem
Maybe this question is confusing to understand ( as I'm not fluent in English ) but,
I need to select
Part 1:
it needs to be ALL cods, except for
Part2:
Another column, with another
it's not the entire query, But after this, I think I can do it.
I tried this:
It works If I have only
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.
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 )
)CadavericoIt 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_Delitois 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:
I've set up an example:
Cadaverico | CorpoDelito
---------: | ----------:
42 | 61
dbfiddle here
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;
GOCadaverico | 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;
GOContext
StackExchange Database Administrators Q#168518, answer score: 14
Revisions (0)
No revisions yet.