how to use not in clause with SUM clause in SQL

Question

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?

what is preventing you from using "not in" and "sum" clauses in the same statement? — Lorentz Vedeler, Apr 10 '16 at 21:07
Possible duplicate of [MySQL "NOT IN" query](http://stackoverflow.com/questions/1519272/mysql-not-in-query) — Jigar, Apr 10 '16 at 21:56

score 1 · Accepted Answer · answered Apr 10 '16 at 21:13

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;

how to use not in clause with SUM clause in SQL

1 Answers1