SQL IS NOT NULL not working

Question

I am attempting to write a short SQL query (in MySQL Workbench) that outputs the number of fails in a subject if it has been failed by more than one student.

Here's my attempt:

SELECT CONCAT(area, yearlevel, code) AS SubjectCode, Count(student)
FROM StudentTakesSubject
WHERE result < 50 AND result <> NULL
GROUP BY code
HAVING Count(Student) > 1;

The problem is it keeps outputting the count which contains the Null record even though I have specified in the query to not count them (or at least I thought I did...).

EDIT : It was the GROUP BY clause that went wrong! As Gordon Linoff has pointed out in his answer, it should have been GROUP BY SubjectCode and that magically solved the problem.

Thank you all for the constructive insights.

Answer 1

As written:

SELECT CONCAT(area, yearlevel, code) AS SubjectCode, Count(student)
FROM StudentTakesSubject
WHERE result < 50 AND result <> NULL
GROUP BY code
HAVING Count(Student) > 1;

This query should return no rows. Why? result <> NULL returns NULL as a boolean value (to a close approximation all comparisons to NULL return NULL). AND NULL evaluates NULL -- and NULL is not true. All rows are filtered out.

The NULL comparison is actually superfluous. The result < 50 will also filter out NULL values.

Looking at the rest of the query, you have another issue. The GROUP BY is on code. It should really be on SubjectCode -- the result of the CONCAT(). In fact, when concatenating different columns, I would recommend using a separator, say CONCAT_WS(':', area, yearlevel, code). Of course a separator may not be desirable for this particular situation.