CODEWITHSUNDEEP
mysqlsqlhibernatehql
redhead
redhead

Reputation: 1317

HQL: count null values in a collection

I need a query which will count null values (of Absence.excuse) in a collection (Student.absences) of some entities (Student).

So far I managed to create this query:

SELECT s, SUM(CASE WHEN a.excuse IS NULL THEN 1 ELSE 0 END)
    FROM Student AS s 
LEFT JOIN s.absences a 
WHERE s.class = :class 
GROUP BY s
ORDER BY s.ordinal

which works but only if a student has already some absences in the Absence table (with or without an excuse). If a student has no absences at all it returns 1 for it. But I don't know how is that possible. Does it evaluate a.excuse to NULL if there is no absence?

Thanks for any help.

Upvotes: 0

Views: 1702

Answers (1)

JB Nizet
JB Nizet

Reputation: 691865

Try with

SELECT s, SUM(CASE WHEN (a.excuse IS NULL and a.id IS NOT NULL) THEN 1 ELSE 0 END)

Upvotes: 2

Related Questions