MYSQL - Query summing 8 identical tables

Asked

Viewed 43 times

1

Good afternoon, I am very new in MYSQL, basically I am learning in network searches, but a question arose 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 are not changed, being possible to change only 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 mount a query to sum all the values per item, of each table, getting 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 this way, but with a terrible "query time", getting to crash 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 very confusing, but in the end it was functional, presenting problems when I added more tables in the database.

Any help is welcome, hug.

mysql query

  • Do you define the database? Why not add a "department" column and use a single table?

  • i define only the ID, which refers to an internal code, and the TYPE, which is standard, the idea would be to change only the values.

  • But you created the system? If yes why have a table for each department, it makes no sense, and if you need to create a new department will do what, create one more table?

  • I created it, but like I said, I have no knowledge of it, and I’m the basis of research starting to learn. I’ll try to set another table the way you advised

1 answer

1

I don’t know if it’s the most beautiful option but I believe that if you use one UNION ALL in the 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’ve helped.

  • 1

    instead of Union should be used Union all, since two tables can have exactly the same records

  • Good observation, I made the suggested change.

  • it worked, thanks for the help, but apparently, as reported above, I think I’m thinking the wrong way, I’ll start from scratch to try to do something cleaner. Thank you very much.

  • Unfortunately in this way it is not possible to create a view, otherwise it would be perfect.

Browser other questions tagged mysql query

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