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.
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.
result NOT NULL instead of result <> NULL.
SELECT CONCAT(area, yearlevel, code) AS SubjectCode, Count(student)
FROM StudentTakesSubject
WHERE result < 50
AND result NOT NULL
GROUP BY code
HAVING Count(Student) > 1;
Videos
I am working on a bunch of orders and I have an order date and the date that the order was "purchased". (The day we processed the order). If we never processed the order because the customer never followed through, there is a blank spot left in the purchased date resulting in a null when I run my query. I attempted to use IS NOT NULL to filter it out in the where statement but nothing happened. Any thoughts?
WHERE DATE(a.ORDER_DATE) BETWEEN DATE(:startDate) AND DATE(:endDate) AND a.PURCH_ORDER_DATE IS NOT NULL;Result of query
EDIT: For all of those who are wondering, I figured out how to sort out the null values.
SELECT DISTINCT
a.ORDER_NUMBER
,a.SALES_CATEGORY
,a.ORDER_DATE
,a.PURCH_ORDER_DATE
,CASE
WHEN DATEDIFF(a.ORDER_DATE,a.PURCH_ORDER_DATE) >= 0 THEN DATEDIFF(a.ORDER_DATE,a.PURCH_ORDER_DATE)
ELSE "null"
END as Lag
FROM
SOBOOK a
WHERE
DATE(a.ORDER_DATE) BETWEEN DATE(:startDate) AND DATE(:endDate)
AND
ceil(DATEDIFF(a.ORDER_DATE,a.PURCH_ORDER_DATE)) = DATEDIFF(a.ORDER_DATE,a.PURCH_ORDER_DATE)
ORDER BY
ORDER_NUMBER
,LagConsider checking documentation:
NULL indicates that the value is unknown. A null value is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a null value and any other value, return unknown because the value of each NULL is unknown.
If you consider that the value of istrue is unknown in the NULL case then it might or might not equal 1.
The expression istrue != 1 then evaluates to unknown
SQL only returns rows where the WHERE clause evaluates to true.
If you are on SQL Server 2022+ you can use
WHERE istrue IS DISTINCT FROM 1
To give the inequality semantics that you want (Fiddle).