CODEWITHSUNDEEP
sql-server
jlodenius
jlodenius

Reputation: 829

Calculating credits with values from different table

I have two tables, first table named Courses including the columns: coursecode & credits. The other table is course_instances, including columns: StudentID, finished_course, courscode.

Now I want to calculate the sum of all credits for every course that has a '1' in the finished_course column and for a specifik StudentID.

Looking for a simple way of doing this, preferably in a function. Using microsoft sql.

Tried something like this, to get the finished courses from a StudentID, I would also need to then calculate the sum of the credits from those courses, but didnt even get that far since this code doesent work.

SELECT coursecode
FROM course_instances where StudentID = 'asd123' and finished_course > 0
JOIN courses
ON course_instances.coursecode=courses.coursecode;

Upvotes: 0

Views: 131

Answers (2)

user2919277
user2919277

Reputation: 246

SELECT sum(courseCredits)
FROM course_instances a
JOIN courses b
ON b.coursecode=a.coursecode
where a.StudentID = 'asd123' and b.finished_course > 0

Upvotes: 1

user2919277
user2919277

Reputation: 246

Here i have grouped by Student ID. If you want for a specific student then provide the student Id as wel in the where clause.

DROP TABLE cources
create table cources
(
    cname varchar (20),
    cCredits float
)
INSERT INTO cources
(
    cname,
    cCredits
)
Values
('FNC', 144.6),
('MTH', 244.6),
('ENG', 344.6),
('GRAM', 744.6),
('QUID', 644.6)

DROP TABLE course_instances;
CREATE TABLE course_instances
(
    StudentID INT, 
    finished_course INT, 
    courscode varchar(10)
)
INSERT INTO course_instances
(
    StudentID, 
    finished_course, 
    courscode
)
Values
(123,1,'FNC'),
(125,0,'FNC'),
(127,0,'FNC'),
(123,1,'ENG'),
(125,1,'ENG'),
(127,1,'ENG'),
(127,1,'QUID'),
(189,0,'QUID'),
(123,1,'QUID'),
(127,1,'MTH')


SELECT sum(c.cCredits) , StudentID
FROM cources c
INNER JOIN course_instances ci
ON ci.courscode = c.cname
WHERE ci.finished_course = 1
GROUP BY 
StudentID

Upvotes: 3

Related Questions