There are 2 related tables in the database.
Contributors :
Events(hereyouentertrainingtimeperemployee):
I need to know the total amount of training time for each industry. The Select below works, but only for column C1. I need to go through the other columns too (c2, c3, c4, c5 etc), however I have not figured out how to do it yet.
SELECT SUM(eventos.tempo)
FROM colaboradores
INNER JOIN eventos
ON colaboradores.id = eventos.c1
In the ON of JOIN clause you can use the IN operator and compare it to all the columns containing the id of the collaborator. As you want to know the total time by sector, you also need to add GROUP BY by sector:
SELECT colaboradores.setor, SUM(eventos.tempo) tempoTotal
FROM colaboradores
INNER JOIN eventos
ON colaboradores.id IN (
eventos.c1, eventos.c2, eventos.c3, eventos.c4, eventos.c5,
eventos.c6, eventos.c7, eventos.c8, eventos.c9, eventos.c10
)
GROUP BY colaboradores.setor
Result
+----------------+
|setor|tempoTotal|
|-----|----------|
| A| 1780|
| E| 2260|
| P| 2860|
| Q| 2860|
| X| 1660|
+----------------+
See working in Sql Fiddle .
Additional Information
Storing event contributors within the eventos table is not ideal for normalization of data . This can cause a lot of problems, one of which is that you limit the amount of employees in an event by 10, if there is one day there, you will have a big job to do.
Since the relation of eventos and colaboradores is N:N it is necessary to create a third table to reference.
But in this case, since the eventos table only contains one column, you could do the inverse, one contributor per line and for each collaborator to store the time:
CREATE TABLE eventos(
id INT PRIMARY KEY AUTO_INCREMENT,
idcolaborador INT,
tempo INT,
CONSTRAINT fk_eventos_colaboradores FOREIGN KEY (idcolaborador) REFERENCES colaboradores (id)
);
In this way your SELECT would look like this:
SELECT colaboradores.setor, SUM(eventos.tempo) tempoTotal
FROM colaboradores
INNER JOIN eventos
ON colaboradores.id = eventos.idcolaborador
GROUP BY colaboradores.setor
See working on Sql Fiddle .
You can create as many conditions as you want in the Inner join
SELECT colaboradores.id, SUM(eventos.tempo)
FROM colaboradores INNER JOIN eventos ON colaboradores.id = eventos.c1
OR colaboradores.id = eventos.c2
OR colaboradores.id = eventos.c3
OR colaboradores.id = eventos.c4
OR colaboradores.id = eventos.c5
OR colaboradores.id = eventos.c6
OR colaboradores.id = eventos.c7
OR colaboradores.id = eventos.c8
OR colaboradores.id = eventos.c9
OR colaboradores.id = eventos.c10
GROUP BY colaboradores.id
Group by is to make the sum per person