The following SQL Sums are not matching up.  Can anyone explain why?

SUM(column_a - (CASE WHEN column_b =1 then column_a ELSE 0 END))
FROM my_table

SUM(column_a)
FROM my_table
WHERE column_b <> 1


The first one should subtract column_a from itself, when column_b = 1.
So it should add up only the SUMS of column_a in records where column_b
<> 1.

The second one should only have rows in it where column_b isn't 1.  The
sum of all column_a in this set should then equal the same.  Shouldn't
it?

It doesn't.  ...Why?

Can you post a simple repro for others to test? Check you column_a for any
NULLs, that might be causing the difference.

There are NULLS in column_b.  But shouldn't the <> 1 restrict in the
same way?

one possible reason: because column_b is nullable

create table #t(column_a int, column_b int)
insert into #t
select 1, null
union
select 2, null
union
select 1, 1
union
select 2, 1
union
select 2, 2
go
select
SUM(column_a - (CASE WHEN column_b =1 then column_a ELSE 0 END))
FROM #t

-----------
5

go
select
SUM(column_a)
FROM #t
WHERE column_b <> 1

-----------
2

Ok...   Able to reproduce the error with a sample

SELECT * FROM my_table

seq_no      column_a    column_b
----------- ----------- -----------
1           5           1
2           7           0
3           2           NULL
4           3           1
5           2           NULL

(5 row(s) affected)


SELECT SUM((column_a - (CASE WHEN column_b = 1 THEN column_a ELSE 0
END)))
FROM my_table


-----------
11

(1 row(s) affected)


SELECT SUM(column_a)
FROM my_table
WHERE column_b <> 1


-----------
7

(1 row(s) affected)

Ok...

See the problem.  It is the NULL affecting it.

This corrects it.

SELECT SUM((column_a - (CASE WHEN column_b = 1 or column_b is null THEN
column_a ELSE 0 END)))
FROM my_table

-----------
7

(1 row(s) affected)

You might simplify the first statement to remove the nesting, and make the
code easier to read and troubleshoot.

select SUM(CASE WHEN column_b =1 then 0 ELSE  column_a END) from my_table
is equivilant to:
SUM(column_a - (CASE WHEN column_b =1 then column_a ELSE 0 END)) from
my_table

The problem, however, is most likely with nulls in your data.

The code above will add up column_a if column b is null, or is any value
other than 1.  The following code will also get the same results.

select sum(column_a) from my_table where column_b <> 1 or column_b is null
select sum(column_a) from my_table where isnull(column_b,0) <> 1

The code you had...
select sum(column_a) from my_table where column_b <> 1 will sum up column_a
when column b has a NON-NULL value, and is not equal to 1.

Show quoteHide quote

....Didn't know, but apparently for "WHERE column_b <> 1"  NULL is not
the same as <> 1.

This can be used to fix it...

SELECT SUM(column_a)
FROM my_table
WHERE (column_b <> 1 OR column_b is NULL)

-----------
11

(1 row(s) affected)

Help with constraint
Table design (search with ranking)
How does engine decide whether to scan or seek index
Nightmare Query
Problem with DateAdd & GetDate()