CODEWITHSUNDEEP
sql-serversql-server-2008sumwhere-clause
4est
4est

Reputation: 3168

Something wrong into where clause

I have below select:

select sum(col1) as sum1 
from table1
where col2 = 'A' and col3 in ('AA','BB')

Now when I did:

select sum(col1) as sum2 
from table1
where col2 <> 'A' and col3 not in ('AA','BB')

and try to add: sum1+sum2, I should got total sum of (col1). But some value is missing. Do you know why?

enter image description here

Upvotes: 0

Views: 58

Answers (2)

HoneyBadger
HoneyBadger

Reputation: 15150

Those two queries are not completely complementary, You are missing NULL values to start. You are also missing WHERE col2 = 'A' AND col3 NOT IN ('AA', 'BB') and WHERE col2 <> 'A' and col3 IN ('AA', 'BB').

Like I said you are also missing NULL, you can check like this: WHERE col2 IS NULL OR col3 IS NULL

edit Requested is the alternate query of the first one given:

select  sum(col1) as sum2 
from    table1
where   col2 <> 'A' 
     OR col3 not in ('AA','BB')
     OR col2 IS NULL
     OR col3 IS NULL

Upvotes: 2

Alrighty then
Alrighty then

Reputation: 127

Because there could be rows where col2 = 'A' and col3 not in ('AA','BB') or col2 <> 'A' and col3 in ('AA','BB')

Upvotes: 0

Related Questions