CODEWITHSUNDEEP
mysqljoinaggregate-functions
Talen Kylon
Talen Kylon

Reputation: 1968

MySQL Query Database

I have two tables Student and Takes

Student : ID, name, dept_name

Takes: ID, course_id, grade

I need to find the ID and name of each student, and their total number of credits taken but do not count where student got an F grade.

This is where I am at so far, but it is wrong in that it will grab a single student name, and total up all the credits as being that students.

select student.ID, student.name, count(course_id) AS credits
FROM student, takes
WHERE student.ID = takes.ID and takes.grade != 'F';

results
ID     name   credits
001     stud1   20

Upvotes: 0

Views: 166

Answers (2)

Linger
Linger

Reputation: 15048

To get the count per student you need to GROUP BY the Student (s.ID, s.name):

SELECT s.ID, s.name, COUNT(*) AS credits
FROM student AS s
INNER JOIN takes AS t ON s.ID = t.ID 
WHERE t.grade != 'F'
GROUP BY s.ID, s.name

I am guessing that certain courses amount for more credits than others. So if you have a field that shows how many credits each course is, you may want to link that table and use SUM(CreditVal) instead of COUNT(*). Something like:

SELECT s.ID, s.name, SUM(c.credits) AS TotalCreditsEarned
FROM student AS s
INNER JOIN takes AS t ON s.ID = t.ID 
INNER JOIN course AS c ON c.ID = t.course_id
WHERE t.grade != 'F'
GROUP BY s.ID, s.name

In the above query just change c.ID = t.course_id to whatever the fields are that link the two tables together.

Upvotes: 2

Rahul
Rahul

Reputation: 77866

Just modify your query like

select s.ID, s.name, count(t.scourse_id) AS Total_Credits
FROM student s join takes t
on s.ID = t.ID and t.grade != 'F'
group by s.id;

Upvotes: 0

Related Questions