Cookie Jar Cookie Jar - 1 year ago 108
MySQL Question

SQL IS NOT NULL not working

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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download