3

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.

  • have you tried with vit_codigotiposexames <> 21 or vit_codigotiposexames not in (21)? – McNets Mar 29 '17 at 15:16
  • hey @McNets, the problem is, that I need two sums() in the same query, with the same column, but with different rules. it would work with 2 queries. – Racer SQL Mar 29 '17 at 15:19
  • 1
    Take @McNets suggestion as one to make the query more easily maintainable and readable. – RDFozz Mar 29 '17 at 15:35
14

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

  • Oh Nice. I will try this and post results.i'm trying to create temp tables – Racer SQL Mar 29 '17 at 15:39
  • 1
    Nice one. I think I will stick with it. Thanks, bro. I was trying to do it with pivot, but it's a lot harder than using case. – Racer SQL Mar 29 '17 at 15:56
  • In PostgreSQL, you can omit the ELSE. A CASE where none of the conditions is true gives back NULL and NULL has no affect on SUM. Is SQL Server the same? – jpmc26 Mar 29 '17 at 22:07
  • 1
    @jpmc26 ELSE NULL is the default in all DBMS. Problem is that if all the summed up values are null, SUM() does not return 0 but NULL. Which is also the SQL default - even if it is mathematically nonsense. – ypercubeᵀᴹ Mar 30 '17 at 10:21
  • @ypercubeᵀᴹ It does the same thing in the event that there are no rows to aggregate. ELSE 0 doesn't help with that. COALESCE(SUM(...), 0) is a more robust way of dealing with a NULL result. If you need to distinguish between no rows to aggregate and no rows matching the CASE statement, then I guess the ELSE 0 could be used for that, but that seems like a rather rare use case. – jpmc26 Mar 30 '17 at 18:07
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.

2

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
  • hey @scott. Yeah, I tried this but it has syntax error because of the as and the ,. Even using the alias as column_name I have the same error. – Racer SQL Mar 29 '17 at 15:24
  • You really should get rid of that huge in list, or the query will return wrong results when someone gets the idea to introduce a new code. – Philipp Mar 29 '17 at 16:51
  • This would work if your first line didn't have * from – Brad Mar 29 '17 at 21:10
  • @RafaelPiccinelli - I realize you've already accepted another answer (and it's definitely better), but I at least wanted to clean up my original offering which had a syntax error. – Scott Hodgin Mar 30 '17 at 9:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.