Loop for adding Mysql fields

Asked

Viewed 681 times

1

Dear, I have two tables, where Tabela1 has columns from A1 to A4, B1 to B4, C1 to C4 and D1 to D4, all of the decimal type (4,2) and with n rows; and the table2 has 3 columns being Total1, Total2 and Total3, where the sum of the fields of Tabela1 must be presented. Roughly I created the following precedent to update the 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();

However this type of repetitive code is not cool, I am trying to use the Precedent below, but the sum does not work for the variable str, follows 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 the possibility of creating this looping.

Thank you.

mysql

  • The problem seems to me to be in concat('A',x) which does not return the reference to a column but the string A1 for example.

  • And the column "column"?

  • column is the same column name, in this column I will have the values A1, A2, A3, A4, B1, B2...up to C4, outside this column I will have the column TOTAL, where the result of the sum of fields should be stored.

  • Actually, I believe the problem is in the Concat, but I couldn’t find a way to use the loop for the 4 variations of A,B,C. I couldn’t find a way to join the strings A,B,C with the variable x, so that the sum recognizes it as the name of a column.

  • @Thiago You need to make one SELECT within the concat() to get the value... I’m running out of time right now, but you can look at this Sqlfiddle, do what you need.

  • Just to keep on understanding. In the first code you have total1, total2 and total3. In the second only total. I understand you actually have a field, too coluna where it’s like spine A1 - total - sum(A1), spine A2 - total - sum(A2), and so on. That’s it?

  • I had already tried using select(Concat()), but sum does not recognize yet.

Show 2 more comments

1 answer

1

TB_DADOS (would be your Tabela1)

| 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 don’t even need to use this second table, for the total.

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 in all its so-called functions in all columns needing all its rows, which could be exhaustive.

For this can be used the precedent and the table to store the total. The corrected precedent to run, 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

Functioning in the Sqlfiddle.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.