Loop for MySql field sum

Question

Loop for MySql field sum

Navigation

#1 by (1 votes)

1

Dear, I have two tables, where table1 has columns from A1 to A4, B1 to B4, C1 to C4 and D1 to D4, all of type decimal (4.2) and with n rows; and table2 has 3 columns being Total1, Total2 and Total3, where the sum of the fields in table1 must be presented. Roughly I created the following procedure to update table2:

    DELIMITER $$
 DROP PROCEDURE IF EXISTS teste$$
 CREATE PROCEDURE teste()
       BEGIN
update tabela2 set TOTAL1 = (select sum(A1) from tabela1) where coluna = 'A1';
update tabela2 set TOTAL1 = (select sum(B1) from tabela1) where coluna = 'B1';
update tabela2 set TOTAL1 = (select sum(C1) from tabela1) where coluna = 'C1';
update tabela2 set TOTAL1 = (select sum(A2) from tabela1) where coluna = 'A2';
update tabela2 set TOTAL1 = (select sum(B2) from tabela1) where coluna = 'B2';
update tabela2 set TOTAL1 = (select sum(C2) from tabela1) where coluna = 'C2';
update tabela2 set TOTAL1 = (select sum(A3) from tabela1) where coluna = 'A3';
update tabela2 set TOTAL1 = (select sum(B3) from tabela1) where coluna = 'B3';
update tabela2 set TOTAL1 = (select sum(C3) from tabela1) where coluna = 'C3';
update tabela2 set TOTAL1 = (select sum(A4) from tabela1) where coluna = 'A4';
update tabela2 set TOTAL1 = (select sum(B4) from tabela1) where coluna = 'B4';
update tabela2 set TOTAL1 = (select sum(C4) from tabela1) where coluna = 'C4';
update tabela2 set TOTAL2 =  (select sum(A1) from tabela1) where coluna = 'A1';
update tabela2 set TOTAL2 =  (select sum(B1) from tabela1) where coluna = 'B1';
update tabela2 set TOTAL2 =  (select sum(C1) from tabela1) where coluna = 'C1';
update tabela2 set TOTAL2 =  (select sum(A2) from tabela1) where coluna = 'A2';
update tabela2 set TOTAL2 =  (select sum(B2) from tabela1) where coluna = 'B2';
update tabela2 set TOTAL2 =  (select sum(C2) from tabela1) where coluna = 'C2';
update tabela2 set TOTAL2 =  (select sum(A3) from tabela1) where coluna = 'A3';
update tabela2 set TOTAL2 =  (select sum(B3) from tabela1) where coluna = 'B3';
update tabela2 set TOTAL2 =  (select sum(C3) from tabela1) where coluna = 'C3';
update tabela2 set TOTAL2 =  (select sum(A4) from tabela1) where coluna = 'A4';
update tabela2 set TOTAL2 =  (select sum(B4) from tabela1) where coluna = 'B4';
update tabela2 set TOTAL2 =  (select sum(C4) from tabela1) where coluna = 'C4';
update tabela2 set TOTAL3 =  (select sum(A1) from tabela1) where coluna = 'A1';
update tabela2 set TOTAL3 =   (select sum(B1) from tabela1) where coluna = 'B1';
update tabela2 set TOTAL3 =   (select sum(C1) from tabela1) where coluna = 'C1';
update tabela2 set TOTAL3 =   (select sum(A2) from tabela1) where coluna = 'A2';
update tabela2 set TOTAL3 =   (select sum(B2) from tabela1) where coluna = 'B2';
update tabela2 set TOTAL3 =   (select sum(C2) from tabela1) where coluna = 'C2';
update tabela2 set TOTAL3 =   (select sum(A3) from tabela1) where coluna = 'A3';
update tabela2 set TOTAL3 =   (select sum(B3) from tabela1) where coluna = 'B3';
update tabela2 set TOTAL3 =   (select sum(C3) from tabela1) where coluna = 'C3';
update tabela2 set TOTAL3 =   (select sum(A4) from tabela1) where coluna = 'A4';
update tabela2 set TOTAL3 =   (select sum(B4) from tabela1) where coluna = 'B4';
update tabela2 set TOTAL3 =   (select sum(C4) from tabela1) where coluna = 'C4';
END$$
DELIMITER ;
all teste();

But this type of repetitive code is not legal, I'm trying to use the procedure below, but the sum does not work for the variable str, follow code:

    DELIMITER $$
DROP PROCEDURE IF EXISTS teste$$
CREATE PROCEDURE teste()
BEGIN
 DECLARE x  INT;
 DECLARE str1 varchar(2);
 DECLARE str2 varchar(2);
 DECLARE str3 varchar(2);
  SET x = 1;
    WHILE x  <= 4 DO
        SET str1 =  concat('A',x);
        SET str2 =  concat('B',x);
        SET str3 =  concat('C',x);
        update tabela set TOTAL = (select sum(str1) from tabela2) where coluna = str1;
        update tabela set TOTAL = (select sum(str2) from tabela2) where coluna = str2;
        update tabela set TOTAL = (select sum(str3) from tabela2) where coluna = str3;
        SET  x = x + 1; 
    END WHILE;
END$$
DELIMITER ;

call teste();

I would like to know if there is a possibility to create this looping.

Thank you.

mysql

asked by anonymous 03.11.2014 / 17:29

1 answer

Doubt of Concept when registering parcels in the database How to do paging with the Instagram API?

score 1 · Answer 1

TB_DADOS (would be your table1)

| A1 | A2 | A3 | A4 | B1 | B2 | B3 | B4 | C1 | C2 | C3 | C4 |
|----|----|----|----|----|----|----|----|----|----|----|----|
|  5 |  1 |  8 |  2 |  9 |  2 | 16 |  1 |  1 | 11 |  3 | 21 |
|  6 |  4 |  4 |  5 |  1 |  7 |  1 |  4 | 17 | 12 |  6 |  9 |
|  7 |  2 |  3 |  4 | 10 |  2 |  5 | 11 |  8 |  2 |  9 | 10 |

TB_TOTAL (would be your table2)

| coluna | total  |
|--------|--------|
|   A1   |  18    |
|   A2   |   7    |
|   A3   |  15    |
|   A4   |  11    |
|   B1   |  20    |
|   B2   |  11    |
|   B3   |  22    |
|   B4   |  16    |
|   C1   |  26    |
|   C2   |  25    |
|   C3   |  18    |
|   C4   |  40    |

In my view, with this structure, you do not even need to use this second table for the total.

You can create a view that returns the total

CREATE VIEW 'vw_total' AS
  SELECT 
    SUM(A1) as A1, 
    SUM(A2) as A2,
    SUM(A3) as A3, 
    SUM(A4) as A4, 
    SUM(B1) as B1, 
    SUM(B2) as B2,
    SUM(B3) as B3, 
    SUM(B4) as B4, 
    SUM(C1) as C1, 
    SUM(C2) as C2,
    SUM(C3) as C3, 
    SUM(C4) as C4
  FROM tb_dados;

The return of this view would be:

select * from vw_total;

| A1 | A2 | A3 | A4 | B1 | B2 | B3 | B4 | C1 | C2 | C3 | C4 |
|----|----|----|----|----|----|----|----|----|----|----|----|
| 18 |  7 | 15 | 11 | 20 | 11 | 22 | 16 | 26 | 25 | 18 | 40 |

To capture the total of a single field:

select A1 from vw_total;

| A1 | 
|----|
| 18 |

However, if this total table is used constantly and the data table has volume, it is not ideal to use this view since it would apply all its functions to all columns needing all of their rows, which could be exhausting.

For this you can use the procedure and the table for total storage. The corrected procedure to execute must use prepare and execute , executing querys defined in string.

delimiter $$

create procedure 'atualiza_total'()
begin
 declare x     int;
 declare str   varchar(2);
 set x = 1;
    while x <= 12 do
        case
          when x <= 4 then 
            set str =  concat('A',x);
          when x <= 8 then 
            set str =  concat('B',x-4);
          else
            set str =  concat('C',x-8);
        end case;

        set @query = concat("update tb_total set total = (select sum(",str,") ","from tb_dados) where coluna = '",str,"'; ");

        prepare stmt1 from @query;
        execute stmt1;

        set  x = x + 1; 
    end while;
end

Running on SQLFiddle .