Adding data from the same column

Question

Adding data from the same column

Navigation

#1 by (4 votes)

3

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

For example: the client wants to buy a quota in the amount of 2000, in the bank I have several quotas registered, 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 find the nearest number directly, through a field (not the sum of fields). example:

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

mysql sql

asked by anonymous 07.11.2018 / 16:01

1 answer

Create a key / values association from user-entered data In CSS what are Implicit or Explicit attributes or attributes? What is the difference between an "Implicit Grid" and an "Explicit Grid"?

score 4 · Accepted Answer

This is necessary to do in programming, but if you really need to do it in MySql, an output would be to set a procedure to calculate and return to you. I 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:

Creating 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|
+----------------------------------+