CODEWITHSUNDEEP
mysqlsqldatabase
ariamckinley
ariamckinley

Reputation: 1122

Limit SQL by the sum of the row's value

So I'm kind of stumped here, I have a table setup like this

+-----------+------+
| Timestamp | Size |
+-----------+------+
|   1-1-13  + 10.3 +
+-----------+------+
|   1-3-13  +  6.7 +
+-----------+------+
|   1-5-13  +  3.0 +
+-----------+------+
|   1-9-13  + 11.4 +
+-----------+------+

And I'm wondering if there's any way to run a query like this

SELECT * FROM table ORDER BY timestamp ASC LIMIT BY (SUM(size) <= 20.0);

This should grab the first three rows, because the sum of the size in of the first 3 rows is 20. However, it might not always be 3 rows that equal 20. Sometimes the first row might have a value of 20, and in that case, it should only grab the first one.

I'm already aware that this it's possible to quickly calculate the sum in PHP after the query is run, but I'm trying to accomplish this with just MySQL.

Upvotes: 12

Views: 6067

Answers (3)

Paul Spiegel
Paul Spiegel

Reputation: 31792

Using window functions (in MySQL 8.0+ and MariaDB 10.2+):

SELECT *
FROM (
  SELECT t.*, SUM(size) OVER (
    PARTITION by NULL
    ORDER BY t.Timestamp
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) as csum
  FROM MyTable t
  ORDER BY t.Timestamp
) x
WHERE csum <= 20;

Or shorter:

SELECT *
FROM (
  SELECT t.*, SUM(size) OVER (ORDER BY t.Timestamp) as csum
  FROM MyTable t
  ORDER BY t.Timestamp
) x
WHERE csum <= 20;

Demo on db-fiddle.com

Upvotes: 1

Hart CO
Hart CO

Reputation: 34774

You want to add a running total, and limit based on that, the following should work:

SET @runtot:=0;
 SELECT 
    q1.t,
    q1.s,
    (@runtot := @runtot + q1.s) AS rt
 FROM 
    (SELECT Date AS t,
     SIZE AS s
     FROM  Table1
     ORDER  BY Date
     ) AS q1
WHERE @runtot + q1.s <= 20

Edit: Demo here - SQL Fiddle

Upvotes: 7

Strawberry
Strawberry

Reputation: 33945

 SELECT * FROM ints ORDER BY i;
 +---+
 | i |
 +---+
 | 0 |
 | 1 |
 | 2 |
 | 3 |
 | 4 |
 | 5 |
 | 6 |
 | 7 |
 | 8 |
 | 9 |
 +---+

 SELECT x.* ,SUM(y.i) FROM ints x JOIN ints y ON y.i <= x.i GROUP BY x.i;
 +---+----------+
 | i | SUM(y.i) |
 +---+----------+
 | 0 |        0 |
 | 1 |        1 |
 | 2 |        3 |
 | 3 |        6 |
 | 4 |       10 |
 | 5 |       15 |
 | 6 |       21 |
 | 7 |       28 |
 | 8 |       36 |
 | 9 |       45 |
 +---+----------+

 SELECT x.* ,SUM(y.i) FROM ints x JOIN ints y ON y.i <= x.i GROUP BY x.i HAVING SUM(y.i) <= 20;
 +---+----------+
 | i | SUM(y.i) |
 +---+----------+
 | 0 |        0 |
 | 1 |        1 |
 | 2 |        3 |
 | 3 |        6 |
 | 4 |       10 |
 | 5 |       15 |
 +---+----------+

Upvotes: 7

Related Questions