ster ster - 3 months ago 16
MySQL Question

MySql multiplication columns with NULL 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 | Prodcut 3 | NULL | 3 | |
+----+-------------+----------+------+-------+


And this query:

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


I want to replace NULL 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

You can use a coalesce expression to get a value instead of a null:

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