ster ster - 3 months ago 17
MySQL Question

MySql multiplication columns with NULL and 0 rows

I have this SQL table:



+----+-------------+----------+------+-------+
| ID | DESCRIPTION | QUANTITY | EACH | PRICE |
+----+-------------+----------+------+-------+
| 1 | Product 1 | 1 | 12 | 1*12 |
| 2 | Product 2 | 2 | 3 | 2* 3 |
| 3 | Product 3 | NULL | 3 | |
| 4 | Product 4 | 0 | 7 | |
+----+-------------+----------+------+-------+





And this query:



SELECT
DESCRIPTION,
QUANTITY,
EACH,
COALESCE(QUANTITY, 1) * EACH AS PRICE
FROM table1





I want to replace NULL and 0 with 1 and I want to make the multiplication in the column PRICE. I don't want to use UPDATE because I can't change the values in the table1.
Thank you!

Answer

Try Below query

SELECT
  DESCRIPTION,
  QUANTITY,
  EACH,
  COALESCE(IF(QUANTITY = NULL OR QUANTITY = 0,1,QUANTITY), 1) * EACH AS PRICE
FROM table1