How to write sum query in jpa?

Question

i have a query in mySql that i want to write in my controller.

SELECT sum(`column`='value') ,b.`date`  FROM table b WHERE b.user= 'user1' and b.`date` between '2019-07-21' and '2019-07-26' group by b.`date`

this is the equivalent in jpa

@Transactional
@Modifying
@Query("SELECT sum(column='value') ,b.date  FROM myclasse b WHERE b.user= :user and b.date between :dt1 and :dt2 group by b.date")
    List<Object[]> function( String user,@Param("dt1")Date dt1,@Param("dt2")Date dt2);

i expect the same result as phpmyadmin result but i get this error:

Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: expecting CLOSE, found '=' near line 1, column 23 [SELECT SUM(column='value') ,b.date FROM com.project.Myclass b WHERE b.user= :user and b.date between :dt1 and :dt2 group by b.date] at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:138) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final] at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final] at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final] at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:729) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final] at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:23) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_161] at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) ~[na:1.8.0_161] at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[na:1.8.0_161] at java.lang.reflect.Method.invoke(Unknown Source) ~[na:1.8.0_161] at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:350) ~[spring-orm-5.1.8.RELEASE.jar:5.1.8.RELEASE] at com.sun.proxy.$Proxy118.createQuery(Unknown Source) ~[na:na] at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:87) ~[spring-data-jpa-2.1.9.RELEASE.jar:2.1.9.RELEASE] ... 76 common frames omitted

Tim Biegeleisen Tim Biegeleisen · Accepted Answer · 2019-07-27T08:34:19

Replace the boolean expression with a CASE expression:

SELECT
    b.date,
    COUNT(CASE WHEN some_col = 'some_value' THEN 1 END) AS cnt
FROM table_b b
WHERE b.user = 'user1' AND b.date BETWEEN '2019-07-21' AND '2019-07-26' 
GROUP BY b.date;

The syntax sum(column = 'value') is particular to MySQL (and maybe a few other databases, such as Postgres), but does not appear to be supported by JPA.

How to write sum query in jpa?

1 Answers