Query Addition with multiple CASE WHEN

[jodybingo]

HI

I am trying to write a query that gives me a total - but man of the fields have null values. I am trying to use the CASE WHEN to assign a zero, if null, and multiply the first value by the second (quantity & price). The moment I add a plus sign and use a second CASE WHEN BASE forgets about the first CASE WHEN and only does the last CASE WHEN (unless there is a value greater than zero in the first CASE WHEN). Here is my sql on this

CASE WHEN ‘ch_pizza’ IS EMPTY THEN 0 ELSE ( qty_ch_pizza * price_ch_pizza ) END +

CASE WHEN ‘qty_pep_pizza’ IS EMPTY THEN 0 ELSE ( qty_pep_pizza * price_pep_pizza ) END +

CASE WHEN ‘qty_ad_pizza’ IS EMPTY THEN 0 ELSE ( qty_ad_pizza * price_ad_pizza ) END



The quantity and amount are on two separate tables. What am I missing? Thanks

W10 - LO version 7.1.2.2 - MySQL 8

[jodybingo]

update:
I erased everything and started from scratch and renamed a few fields on my pricing table and it is now calculating everything (so far).

[Ratslinger]

Hello,

See you comment but do have another suggestion. Instead use Coalesce:

Coalesce(qty_ch_pizza, 0) * price_ch_pizza +
 Coalesce(qty_pep_pizza, 0) * price_pep_pizza +
 Coalesce(qty_ad_pizza, 0) * price_ad_pizza As "Total Order"

[jodybingo]

works too! and a lot less typing.
Thanks Ratslinger