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 (
price_ch_pizza ) END +
CASE WHEN ‘qty_pep_pizza’ IS EMPTY THEN 0 ELSE (
price_pep_pizza ) END +
CASE WHEN ‘qty_ad_pizza’ IS EMPTY THEN 0 ELSE (
price_ad_pizza ) END
The quantity and amount are on two separate tables. What am I missing? Thanks
W10 - LO version 22.214.171.124 - MySQL 8
I erased everything and started from scratch and renamed a few fields on my pricing table and it is now calculating everything (so far).
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"
works too! and a lot less typing.