Using Sum in If in Mysql

Question

I want to select the sum of values of my_field. The unit of my_field is in seconds. I don't want to exceed 24 hours, so when it exceeds 24 hours, I want it to select 24*60*60 = 86400.

SELECT IF(SUM(my_field) > 86400, 86400, SUM(my_field))
...

This solution doesn't seem to work. What else can I do?

Mureinik · Accepted Answer · 2015-06-22 15:10:58Z

3

Using least would be much easier:

SELECT LEAST(SUM(my_field), 86400)
FROM   my_table

edited Jun 22, 2015 at 15:10

answered Jun 22, 2015 at 14:33

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

jarlh Over a year ago

Not SMALLEST... (or is it LEAST?)

tolga Over a year ago

Thanks for the answers, this gave me the idea.

Mureinik Over a year ago

@jarlh yup, that was a momentarily lapse in concentration. Indeed - least

Mureinik Over a year ago

@StuartLC yup - wasn't concentrating. See latest edit.

Mureinik Over a year ago

@tolga note that as noted in the above comment, it should have been least, not greatest (edited to update the answer).

Rahul · Accepted Answer · 2015-06-22 14:41:02Z

2

Another alternative is to use CASE expression like

SELECT CASE WHEN SUM(my_field) > 86400 THEN 86400 ELSE SUM(my_field) END
FROM table1

answered Jun 22, 2015 at 14:41

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

1 Comment

tolga Over a year ago

Thank you, this answer is useful too.

Collectives™ on Stack Overflow

Using Sum in If in Mysql

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related