I would like to add the points of the classes of all the teachers and create a rank with the 3 majors. My problem is being in QUERY SQL to return the data to my system.
Follow the image of my bank.
Considering the following table, based on the one displayed in the question:
create table aula (
'id' int unsigned auto_increment primary key,
'data' date,
'titulo' varchar(255),
'professor_id' int,
'votos' int,
'pontos' int,
'status' int(1)
);
Populating the table with some example records:
insert into aula ('data', 'titulo', 'professor_id', 'votos', 'pontos', 'status') values ("2017-04-12", "Aula 1", 3, 8, 33, 0);
insert into aula ('data', 'titulo', 'professor_id', 'votos', 'pontos', 'status') values ("2017-04-12", "Aula 2", 3, 2, 4, 0);
insert into aula ('data', 'titulo', 'professor_id', 'votos', 'pontos', 'status') values ("2017-04-12", "Aula 3", 7, 22, 62, 0);
insert into aula ('data', 'titulo', 'professor_id', 'votos', 'pontos', 'status') values ("2017-04-12", "Aula 4", 7, 2, 7, 0);
insert into aula ('data', 'titulo', 'professor_id', 'votos', 'pontos', 'status') values ("2017-04-12", "Aula 5", 6, 0, 0, 0);
insert into aula ('data', 'titulo', 'professor_id', 'votos', 'pontos', 'status') values ("2017-04-12", "Aula 6", 6, 0, 0, 0);
insert into aula ('data', 'titulo', 'professor_id', 'votos', 'pontos', 'status') values ("2017-04-12", "Aula 7", 8, 0, 1, 0);
You can search for the top three teachers by doing:
select 'id', 'professor_id', sum('pontos') as 'total'
from aula
group by 'professor_id'
order by sum('pontos')
desc limit 3;
That is, the id
, professor_id
, and total points are selected based on the pontos
column. The records are grouped by professor_id
, sorted by the sum of points and limited to the three records. In this way, the result will be:
| id | professor_id | total |
|----|--------------|-------|
| 3 | 7 | 69 |
| 1 | 3 | 37 |
| 7 | 8 | 1 |
You can see the code working here .