Java Spring JPA: How to find the sum of only non null values in a column?

Question

I currently have a query like this:

SELECT DISTINCT t.column1, SUM(t2.column2 IS NOT NULL)
FROM table t
LEFT OUTER JOIN table t2 on table.id = t2.id
GROUP BY column1, column2;

I am trying to implement the query using Spring JPA CriteriaBuilder. I see the CriteriaBuilder.sum() method, but I don't see a way to apply the IS NOT NULL part to the selection. Column2's data type is string.

Sample of my code

criteriaBuilder.multiselect(root.get("column1"), cb.sum(root.get("column2")));

It sums the number of non null records @Bohemian The query works as expected in MYSQL. — Matthew S., Sep 10 '21 at 13:05
@mvmn If I add "t2.column2 IS NOT NULL" into the WHERE clause, the left outer join will not work as expected. Adding a WHERE clause to the right table will cause the left outer join to be cancelled out. — Matthew S., Sep 10 '21 at 13:08
@ScaryWombat I had to use GROUP BY in order to use SUM since it is an aggregated query — Matthew S., Sep 10 '21 at 13:09

Bohemian · Answer 1 · 2021-09-10T21:28:28.093

2

Only in MySQL would such a query run, due MySQL’s relaxed syntax rules.

In particular, in mysql sum(column2 is not null) is a count, not a sum. The expression column2 is not null is boolean and in mysql false is 0 and true is 1, so summing this expression is a mysql hack to count the number of times column2 is not null.

To convert it to standard sql:

select
    t.column1,
    count(t2.column2)
from table t
left join table t2 on t.id = t2.id
group by t.column1

This works because count() (and all aggregate functions) ignore nulls.

This version also corrects the errant column in the group by clause - in any other database, your query would have produced a “grouping by aggregate expression” error.

This query will produce the same result in MySQL as your current query.

edited Sep 10 '21 at 21:28

answered Sep 09 '21 at 22:55

Bohemian

412,405
93
575
722

Then how does the query work as expected in MYSQL? In MYSQL, SUM(column2 is not null) will sum the number of non null records in column2. – Matthew S. Sep 10 '21 at 13:11
1

@MatthewS. in mysql, `SUM(column2 is not null)` is a *count*, not a *sum*. The expression `column2 is not null` is boolean and in mysql false is 0 and true is 1, so summing this expression is a mysql hack to *count* the number of times `column2` is not null. The non-hack, and portable, way is `sum(case when column2 is null then 0 else 1 end)`. Does that make sense and solve your problem? – Bohemian Sep 10 '21 at 17:22
That makes sense but that doesn't solve my problem of trying to convert the MYSQL query into something that can be used for Criteria Builder. – Matthew S. Sep 10 '21 at 20:00

score 1 · Accepted Answer · answered Sep 15 '21 at 20:05

I was able to find a solution to my problem. Thanks to @bohemian for helping me write a correct sum expression.

 final CriteriaBuilder cb = em.getCriteriaBuilder();
 final CriteriaQuery<Model1> cq = cb.createQuery(Model1.class);
 final Root<Model1> root = cq.from(Model1.class);
 final Join<Model1, Model1> selfJoin = 
     root.join("tableJoinColumn", JoinType.LEFT);
selfJoin.on(...);
cq.multiselect(root.get("column1"), cb.sum(cb.selectCase()
    .when(cb.isNull(selfJoin.get("column2")), 0).otherwise(1).as(Long.class)));
...

The self join required me to create an additional property on my model. Model1.java

    /**
     * Property for LEFT INNER JOIN.
     */
    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name="id")
    private Model1 tableJoinColumn;

How to use JPA CriteriaBuilder selectCase() so that it can have Predicate as result?

Self join in criteria query

Java Spring JPA: How to find the sum of only non null values in a column?

2 Answers2