So I have a MySQL database I use for a software I have made where teachers can upload assignments, students can deliver their assignments and teachers can evaluate these deliveries. Now I want to list a overview over which assignment has the most comments on the deliveries in descending order.
All assignments are stored in a table called module where module_id is PK. All deliveries are stored in a table called delivery where delivery_id is PK and both user_id and module_id is FK. Last but not least we have the student_comment table where all comments are stored. In that table Student_comment_id is PK and delivery_id is FK. If my explanation was horrible, I`ve listed under how I made the tables. There are obviously more tables but I think they are irrelevant to this question.
create table module(
module_id int not null auto_increment,
module_name varchar(50) not null,
create table delivery(
delivery_id int not null auto_increment,
foreign key(module_id) references module(module_id),
foreign key(users_id) references users(users_id)
create table student_comment(
student_comment_id int not null auto_increment primary key,
foreign key(delivery_id) references delivery(delivery_id));
An aggregate query should produce what you're asking for. When you say "which module have the most comments on the deliveries," I interpret that to mean that you want to know the number of comments on all of the deliveries. The following SQL should produce that for you.
select m.module_id, count(c.student_comment_id) as comment_count from module as m inner join delivery as d on d.module_id=m.module_id inner join student_comment as c on c.delivery_id=d.delivery_id group by m.module_id order by count(c.student_comment_id) desc;