I want to add all the points of all teachers and create a rank with the 3 best ones

-1

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.

    
asked by anonymous 12.04.2017 / 18:04

1 answer

0

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 .

    
12.04.2017 / 18:49