MySQL select rows where the sum of the rows is at least a value

January 7, 2020

Example table:

CREATE TABLE a
  (`id` int, `value` decimal(5,2))
;
	
INSERT INTO a
  (`id`, `value`)
VALUES
  (1, 5),
  (2, 1.25),
  (3, 3.7),
  (4, 17.3)
;

Goal – getting the rows in which the sum of value is 9.5

SET @counter := 0;

SELECT
  a.*,
  IF(@counter <= 9.5, 1, 0) AS included,
  (@counter := @counter + a.value) AS valueSum

FROM a
HAVING included = 1

The result:

id value included valueSum
1 5.00 1 5.00
2 1.25 1 6.25
3 3.70 1 9.95

posted in sql by Ivan Gospodinow

Follow comments via the RSS Feed | Leave a comment | Trackback URL

Leave Your Comment

 
Powered by Wordpress and MySQL. Theme by Shlomi Noach, openark.org