We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question
0

Query Addition with multiple CASE WHEN

asked 2021-05-11 17:00:56 +0200

jodybingo gravatar image

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

edit retag flag offensive close merge delete

Comments

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).

jodybingo gravatar imagejodybingo ( 2021-05-11 18:44:07 +0200 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2021-05-11 18:59:51 +0200

Ratslinger gravatar image

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"
edit flag offensive delete link more

Comments

works too! and a lot less typing. Thanks Ratslinger

jodybingo gravatar imagejodybingo ( 2021-05-11 19:12:22 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2021-05-11 17:00:56 +0200

Seen: 23 times

Last updated: May 11