Search code examples
mysqlmaxrowlimit

Limit the maximum value returned for each row

Assuming I have a table as follows:

|id | duration | timeout
=========================
| 1 |   100    |   0
| 2 |  12345   |   1
| 3 |   321    |   0

I would like to get the total duration, as well as the number of sessions and number of timeouts, like so:

SELECT count(id) session_count, sum(duration) duration, count(timeout) timeouts

But for any session that timed out, I need to limit the value to 1000. so my query should produce something like:

| session_count | duration | timeouts
=====================================
|     3         |  1421    |    1

Thanks in advance.

Solution

  • here you go, also changed count(timeout) to sum(timeout)what the if does is check value of timeout if 0 then use duration, otherwise use 1000

    SELECT COUNT(id) session_count, SUM(IF (timeout = 0, duration,1000)) duration, SUM(timeout) timeouts