How to include values in a MySQL sum based on a conditional statement?

Question

I have the following MySQL query which takes in the sum of columns 1-4, from the start of the current date, up to the current time. My table has 5 columns, and I am trying to perform a conditional query based on a value in column_5.

I need to modify this query according to the following:

• If column_5 > 0, then I don't want to include the values from column_1 in my sum, Total.
• But if column_5=0, then I do want to include the values from column_1 in my sum Total.

At the moment, the values from column_1 are included in my sum, regardless of whether column_5 is greater than or equal to 0.

---

SELECT (column_1+column_2+column_3+column_4) at 'Total' 
FROM myDB.table_1
WHERE TS >= CURDATE() AND TS < CURDATE() + INTERVAL 1 DAY ;

Answer 1

Conditionals can be done by means of the CASE operator/expression:

Your query will work with:

SELECT 
 ts, 
 (CASE WHEN column_5 > 0 
 THEN
 0 /* We are ignoring column_1 */
 ELSE
 column_1 /* We are using its value */
 END) + column_2 + column_3 + column_4 AS total
FROM
 t
ORDER BY 
 ts ;

You can check it at http://rextester.com/IHM39024

The CASE expression is standard SQL.

MySQL also offers an IF function that could be used in this case. In fact IF(a,b,c) = CASE WHEN a THEN b ELSE c END.