CODEWITHSUNDEEP
sqlsqlite
k0jir0
k0jir0

Reputation: 3

SQL WHERE IS NULL or column = 'value'

Currently facing a problem using SQLite3 wherein

SELECT item_id, SUM(qoh), MIN(price), MAX(price), item_size
FROM inventory
WHERE item_size IS NULL OR item_size = 'S';

the above query doesn't work in that it will return only NULL values and not S.

SELECT item_id, SUM(qoh), MIN(price), MAX(price), item_size
FROM inventory
WHERE item_size = 'S'

the above obviously returns S

SELECT item_id, SUM(qoh), MIN(price), MAX(price), item_size
FROM inventory
WHERE item_size = 'S'OR item_size IS NULL;

the above returns only NULL values

I'm trying to accomplish the equivalent of below,

SELECT item_id, SUM(qoh), MIN(price), MAX(price), item_size
FROM inventory
WHERE item_size IS NULL;

SELECT item_id, SUM(qoh), MIN(price), MAX(price), item_size
FROM inventory
WHERE item_size = 'S';

but in one command, because of the nature of WHERE column IS NULL OR column = 'value', only ever returns either NULL or 'value' due to the nature of null effecting the algorithm,

are there any overarcing/unknown to me SQL laws that'd allow me to bypass WHERE item_size IS NULL OR item_size = 'S'; fundamentally not working?

Upvotes: 0

Views: 464

Answers (1)

Patrick Artner
Patrick Artner

Reputation: 51683

Use

SELECT item_id, SUM(qoh), MIN(price), MAX(price), item_size
FROM inventory
WHERE coalesce(item_size, 'S') = 'S'
GROUP BY  coalesce(item_size, 'S') -- will add nulls to S or
-- GROUP BY  coalesce(item_size, 'null') - will add them as 'null' but filter them out by your where

Your problem is that using sum does an implicit group by - and null does not group well with strings - you must explicitly group by the coalesced value to group nulls to S

Upvotes: 1

Related Questions