ndev ndev -4 years ago 109
MySQL Question

mysql - SELECT until sum reaches a number

I need to select records until the total of a column reaches a variable number.

I have a query that sort of works but not 100%. I also want to see the call_date and I want the count to be done in order of date.

SELECT NULL AS inbound_duration, NULL AS total
FROM dual
WHERE (@total := 0)
UNION
SELECT inbound_duration, @total := @total + inbound_duration AS total
FROM `records` where calling_user = '1' and call_date LIKE '2016-05-%%' and @total < 5000 ORDER BY call_date


http://sqlfiddle.com/#!9/2e74ff

Answer Source

I tried this (pay attention: you have two rows with same call_date: I think that the order obtained cannot be deterministic, unless you specify other criterias in order by - ex. inbound_duration or some other field):

# DROP TABLE records;
CREATE TABLE `records` (
  `inbound_duration` varchar(5) COLLATE utf8_unicode_ci NOT NULL,
  `call_date` datetime NOT NULL,
  `calling_user` varchar(25) COLLATE utf8_unicode_ci NOT NULL
);

INSERT INTO records
    (inbound_duration, call_date, calling_user)
VALUES
    (100, '2016-05-05 00:00:00', 1),
    (1000, '2016-05-01 00:00:00', 1),
    (900, '2016-05-03 00:00:00', 1),
    (1500, '2016-05-02 00:00:00', 1),
    (2000, '2016-05-04 00:00:00', 1),
    (2500, '2016-05-05 00:00:00', 1)
;
SELECT * FROM records  ORDER BY call_date;


 SELECT NULL AS call_date, NULL AS inbound_duration, NULL AS total
  FROM dual
 WHERE @total := 0 
 UNION ALL
SELECT call_date, inbound_duration,  @total := @total + inbound_duration AS total
 FROM (SELECT * FROM records  ORDER BY call_date) C where calling_user = '1' and call_date LIKE '2016-05-%%' and @total < 5000 

;

DROP TABLE records;

Output:

    inbound_duration    call_date   calling_user
1   1000    01.05.2016 00:00:00 1
2   1500    02.05.2016 00:00:00 1
3   900 03.05.2016 00:00:00 1
4   2000    04.05.2016 00:00:00 1
5   100 05.05.2016 00:00:00 1
6   2500    05.05.2016 00:00:00 1

    call_date   inbound_duration    total
1   01.05.2016 00:00:00 1000    1000
2   02.05.2016 00:00:00 1500    2500
3   03.05.2016 00:00:00 900 3400
4   04.05.2016 00:00:00 2000    5400
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download