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

Asked

Viewed 155 times

-1

I would like to sum up the points of all teachers' classes and create a rank with the 3 largest. My problem is creating QUERY SQL to return the data to my system.

Here’s a picture of my bank.

inserir a descrição da imagem aqui

mysql
  • 1

    Send the query you already created and return it

1 answer

0


Considering the following table, based on the question displayed:

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 pick up the three highest scoring 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 fields are selected id, professor_id and the total points based on the column pontos. The records are grouped by professor_id, ordered according to the sum of points and limited to the three records. Thus, the result will be:

| id | professor_id | total |
|----|--------------|-------|
|  3 |            7 |    69 |
|  1 |            3 |    37 |
|  7 |            8 |     1 |

You can see the code working here.

  • Thank you very much friend.

Browser other questions tagged mysql

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