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

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 ;

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.

StackExchange Database Administrators Q#163918, answer score: 2