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

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开发者_如何学Python

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?

---

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)

---

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.

---

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