MYSQL - Query adding 8 identical tables

Question

MYSQL - Query adding 8 identical tables

Navigation

#1 by (1 votes)

1

Good afternoon, I'm pretty new to MYSQL, basically I'm learning about searching the net, but there was an issue that I could not solve.

I have about 6 tables, in each I have 10 fixed and identical entries in the field ID and TYPE, which do not change, being possible to only change the values of these fields.

Example:

Dep1
ID  TIPO    VALOR1  VALOR2  VALOR3  VALOR4
1   Camisa  10        20       30   40
2   Calça   15        25       35   45
3   Blusa   3          2       9    25

Dep2
ID  TIPO    VALOR1  VALOR2  VALOR3  VALOR4
1   Camisa     5      10       15   3
2   Calça     30       5       5    10
3   Blusa      9       1       5    5

Dep3
ID  TIPO    VALOR1  VALOR2  VALOR3  VALOR4
1   Camisa     5       10     15    3
2   Calça      30      5       5    10
3   Blusa      9       1       5    5

E assim por diante....

The idea is to be able to set up a query to sum all the values per item, of each table, obtaining the following result:

    deptotal (id, tipo, total1,total2,total3,total4,totalgeral)

ID  TIPO    TOTAL1  TOTAL2  TOTAL3  TOTAL4  VALORTOTAL
1   Camisa    20      40      60      46      166
2   Calça     75      35      45      65      220
3   Blusa     21       4      19      35      79

I tried the following, but with a terrible query time, getting to hang the server for a few moments:

    select 
    (dep1.id) as ID,
    (dep1.tipo) as TIPO,
    (dep1.valor1)+(dep2.valor1)+(dep3.valor1)+(dep4.valor1)+(dep5.valor1)+(dep6.valor1) as total1,
    (dep1.valor2)+(dep2.valor2)+(dep3.valor2)+(dep4.valor2)+(dep5.valor2)+(dep6.valor2) as total2,
    (dep1.valor3)+(dep2.valor3)+(dep3.valor3)+(dep4.valor3)+(dep5.valor3)+(dep6.valor3) as total3,
    (dep1.valor4)+(dep2.valor4)+(dep3.valor4)+(dep4.valor4)+(dep5.valor4)+(dep6.valor4) as total4,
(dep1.valor1)+(dep2.valor1)+(dep3.valor1)+(dep4.valor1)+(dep5.valor1)(dep6.valor1)+(dep1.valor2)+(dep2.valor2)+(dep3.valor2)+(dep4.valor2)(dep5.valor2)+(dep6.valor2)+  (dep1.valor3)+(dep2.valor3)+(dep3.valor3)+ (dep4.valor3)+(dep5.valor3)+(dep6.valor3)+(dep1.valor4)+(dep2.valor4)+ (dep3.valor4)+(dep4.valor4)+(dep5.valor4)+(dep6.valor4) as VALORTOTAL

    FROM
    dep1,dep2,dep3,dep4,dep5,dep6
    where (dep1.id = dep2.id = dep3.id = dep4.id = dep5.id = dep6.id)

Good friends, maybe the table was pretty confusing, but in the end it was functional, having problems when I added more tables in the database.

Any help is welcome, hug.

mysql query

asked by anonymous 10.02.2017 / 19:55

1 answer

Replace HTML entities inside a Div Listing data from a table with PHP

score 1 · Answer 1

I do not know if it is the most beautiful option but I believe that if you use a UNION ALL in tables for example;

SELECT juncao.ID,
       juncao.TIPO, 
       SUM(juncao.valor1) total1,
       SUM(juncao.valor2) total2,
       SUM(juncao.valor3) total3,
       SUM(juncao.valor4) total4,
       SUM(juncao.valor5) total5,
       SUM(juncao.valor1 + juncao.valor2 + juncao.valor3 + juncao.valor4 + juncao.valor5) totalgeral
FROM
     ((SELECT * FROM Dep1)
      UNION ALL
      (SELECT * FROM Dep2)    
      UNION ALL
      (SELECT * FROM Dep3)    
      UNION ALL
      (SELECT * FROM Dep4)    
      UNION ALL
      (SELECT * FROM Dep5)
     ) as juncao
GROUP BY juncao.ID,juncao.TIPO

I hope I have helped.