CODEWITHSUNDEEP
sql
Maikel
Maikel

Reputation: 31

how to use not in clause with SUM clause in SQL

I have these tables 

student_record              grades                      courses
----------------         ----------------------       ---------------------
ID        grade       course_no   letter  points      course_no   code  CRD
----------------         ----------------------       ---------------------
2255       A+             1        A+      4            1         COE200  4         
2255       B+             2        B+     3.5           2         COE305  3
2255       NP             3        NP      0            3         COE400  4
2255       NF             4        NF      0            4         COE405  3

the I wrote this query

SELECT 
    student_record.id,
    SUM(courses.crd * grades.points) AS sum_grade_credits
FROM grades 
    INNER JOIN student_record ON grades.letter = student_record.grade
    INNER JOIN courses ON courses.course_no = student_record.course_no 
 WHERE student_record.id=2255;

how I can let this query do not retrieve data when it see (NP or NF) ?

I mean at (NP or NF)situation the query do not do any operations?

Upvotes: 0

Views: 31

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

You can use the in clause in where clause 

SELECT 
student_record.id,
SUM(courses.crd * grades.points) AS sum_grade_credits
FROM grades 
INNER JOIN student_record ON grades.letter = student_record.grade
INNER JOIN courses ON courses.course_no = student_record.course_no 
WHERE student_record.id=2255
AND student_record.grade not in ('NP', 'NF');

or you can use in join condition 

SELECT 
student_record.id,
SUM(courses.crd * grades.points) AS sum_grade_credits
FROM grades 
INNER JOIN student_record ON (grades.letter = student_record.grade 
  and student_record.grade  not in ( 'NP', 'NF'))
INNER JOIN courses ON courses.course_no = student_record.course_no 
WHERE student_record.id=2255;

Upvotes: 1

Related Questions