user1795074 user1795074 - 2 months ago 15
MySQL Question

SUM minutes with condition

I have a table that contains duration in minutes.
I would like to add them but when the duration is less than 45 min to return 60 min.

My query returns nothing and is like that:

SUM(CASE WHEN duration = 45 THEN duration=60 ELSE 0 END) AS t_dur1 ,
SUM(CASE WHEN duration > 45 THEN duration ELSE 0 END) AS t_dur2


Any thoughts?

Answer

Taking your logic literally, you only need a single CASE statement:

SUM(CASE WHEN duration < 45 THEN 60 ELSE duration END) AS t_dur

This counts durations which are less than 45 minutes as being 60 minutes, otherwise it counts the original value of the duration.

You are probably using this in a GROUP BY query looking something like this:

SELECT some_col,
       SUM(CASE WHEN duration < 45 THEN 60 ELSE duration END) AS t_dur
FROM yourTable
GROUP BY some_col
Comments