MySQL Multiplication in select depending on column value

Question

In MySQL, I would like to make a choice and get the sum, but the multiplication should depend on the value of one of the columns. So this is a regular statement:

  SELECT col1 * col2 AS sum

But if col2 < 0 , col2 should take 0 as a value, so something like: SELECT col1*(col2 < 0 ? 0 : col2) AS sum

Can I do this in MySQL?

Answer 1

Everything that is multiplied by 0 is 0, so it is a bit simpler than your requested logic:

 SELECT CASE WHEN COL2 > 0 THEN COL1 * COL2 ELSE 0 END AS `sum`... 

http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html

Answer 2

In a very compact and elegant solution

 SELECT IF(col2>0,col1*col2,0) as SUM;