Tommy Riska Tommy Riska - 18 days ago 9
SQL Question

Need to list how many comments a delivery has in MySQL

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,
description varchar(1000),
rights int,
primary key(module_id)
);

create table delivery(
delivery_id int not null auto_increment,
module_id int,
users_id int,
delivery_status int,
date_delivered date,
date_approved date,
primary key(delivery_id),
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,
s_content varchar(100),
delivery_id int,
foreign key(delivery_id) references delivery(delivery_id));


So again, I want to list a overview over which module have the most comments on the deliveries and I want to sort it in a descending order. I have tried this for a while now but cant really figure out how to do this. Any help is really appreciated!

Also, I have just started with SQL so if there is something you think is wrong or you have a great source of information which can help me, feel free to comment.

Answer

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;