Reputation: 3
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
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