CODEWITHSUNDEEP
mysql
joh-3
joh-3

Reputation:

MySQL multiplication in select depending on value of column

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

SELECT col1*col2 AS sum

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

Can I do this in MySQL?

Upvotes: 4

Views: 21009

Answers (2)

Leonardo Hermoso
Leonardo Hermoso

Reputation: 838

In a very compact and elegant solution 

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

Upvotes: 1

Dan Grossman
Dan Grossman

Reputation: 52372

Anything multiplied by 0 is 0, so it's a little 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

Upvotes: 8

Related Questions