SQL Math Issue During Sum - Works when removing either of the conditions

Question

I'm using MS SQL, and here is my query...

SELECT SUM(Quantity) FROM table1
WHERE [item_no]='0052556'
AND [qty_to_ship] > 0
AND [type] = 5

Interestingly, the query as it stands above returns 'NULL'...however, if I remove either one of the last 2 conditions it works fine (returns a real number)

to clarify, these queries work fine:

SELECT SUM(Quantity) FROM table1
WHERE [item_no]='0052556'
AND [type] = 5

and

SELECT SUM(Quantity) FROM table1
WHERE [item_no]='0052556'
AND [qty_to_ship] > 0

also note: 'type' is a PK, int, not null

what might be causing this?

Answer 1

Do you have a record that fullfils all the requirement in your (most restrictive) WHERE?

If not - there's your anwer.

Edit: RedFilter's comment is correct is well. You can add your own logic (ISNULL, etc.) in the SELECT Portion to change NULL to values you like (like 0, for instance)

Answer 2

Simply, there are no records that match both clauses

[qty_to_ship] > 0
AND [type] = 5

or the records that do match contain NULL in the Quantity field.

Answer 3

Please check if the below query returns you any rows.

SELECT * FROM table1 
WHERE [item_no]='0052556' 
AND [qty_to_ship] > 0 
AND [type] = 5 

If this is returning you atleast one row, then the SUM should not return NULL