CODEWITHSUNDEEP
mysqlsqlselectnull
ster
ster

Reputation: 199

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.

Upvotes: 2

Views: 1454

Answers (3)

kjmerf
kjmerf

Reputation: 4345

You can also use IFNULL:

SELECT id, description, quantity, each,
IFNULL(quantity,1)*each AS price
FROM Table

Upvotes: 0

Mureinik
Mureinik

Reputation: 311768

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

Upvotes: 1

juergen d
juergen d

Reputation: 204854

COALESCE returns the first non-null value of its parameters

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

Upvotes: 1

Related Questions