CODEWITHSUNDEEP
javamysqlspring-data-jpa
Matthew S.
Matthew S.

Reputation: 331

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

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")));

Upvotes: 4

Views: 1745

Answers (2)

Matthew S.
Matthew S.

Reputation: 331

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

Upvotes: 1

Bohemian
Bohemian

Reputation: 425448

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.

Upvotes: 2

Related Questions