CODEWITHSUNDEEP
sqlfirebirdfirebird2.5
Michael Müller
Michael Müller

Reputation: 401

Firebird SQL IF in where

I want to select the amount from products from (stock) shelf1 and shelf 2 but also list items from shelf3 but list amounts of shelf3 as amount = 0.

SELECT item, amount 
FROM prodcuts 
WHERE kind = 'bike' 
and (stock = 'shelf1' or stock = 'shelf2' )

.

Table products
item    kind    stock   amount
cruiser bike    shelf1  1
cruiser bike    shelf2  2
racing  bike    shelf3  4

Result is

cruiser 3

But I need result

cruiser 3
racing  0

Upvotes: 0

Views: 1768

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

Your result would seem to be:

select p.item, sum(case when p.stock = 'shelf3' then 0 else p.amount end)
from products p
group by p.item;

I'm not quite sure how this fits in with your explanation, however.

Upvotes: 3

asmgx
asmgx

Reputation: 8004

try this

SELECT item, CASE WHEN stock = 'shelf3' THEN 0 ELSE amount END AS amount
FROM prodcuts 
WHERE kind = 'bike' 
and (stock = 'shelf1' or stock = 'shelf2'' or stock = 'shelf3' )

Upvotes: 0

Related Questions