Adding data from the same column

Question

Is there any way to make a calculation(sum) using values from the same column?

For example: the customer wants to buy a quota in the value of 2000, in the bank I have several registered quotas, among them one in the value of 1000, another in the value of 600 and a third in the value of 400.

id      valor        admin
200     400          bb
333     500          caixa
356     1000         bb
567     600          bb
788     350          bb
900     200          caixa

In attempts, I found the nearest number directly, through a field (not the sum of fields). example:

select * from produtcs order by ABS(value - 2000)

Answer 1

This is necessary to do in programming, but if you really need to do it in Mysql, an output would be to mount a procedure that calculates and returns to you. I’ve put together a very simple one that works for the case mentioned in the question, but needs to be improved for other treatments, it’s basically a light of what can be done:

Creation of PROCEDURE

DELIMITER $$

DROP PROCEDURE IF EXISTS selectCotas$$

CREATE PROCEDURE selectCotas(cota int(11))
BEGIN
    DECLARE _id INT(11);
    DECLARE _valor INT(11);
    DECLARE restante INT(11) DEFAULT cota;
    DECLARE ids TEXT DEFAULT '';
    DECLARE fim TINYINT(1) DEFAULT 0;
    DECLARE curs CURSOR FOR SELECT id, valor FROM cotas WHERE valor <= cota ORDER BY valor DESC;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET fim = 1;

    OPEN curs;

    REPEAT
        FETCH curs INTO _id, _valor;

        IF (fim = 0) THEN
            IF (_valor <= restante) THEN
                SET restante = restante - _valor;
                IF (ids = '') THEN
                    SET ids = _id;
                ELSE
                    SET ids = CONCAT(ids, ', ', _id);
                END IF;
            END IF;
        END IF;
    UNTIL (fim or restante = 0) END REPEAT;

    CLOSE curs;

    SET @sql = CONCAT('SELECT * FROM cotas WHERE id IN (', ids, ')');
    PREPARE stmt1 FROM @sql;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
END$$

DELIMITER;

Running

call selectCotas(2000);

Return

+----------------------------------+  
|   id   |   valor   |    admin    |  
|     200|        400|           bb|
|     356|       1000|           bb|
|     567|        600|           bb|
+----------------------------------+