How to distinct counting with Spring JPA Repository

Question

How do I do a distinct counting with Spring Data JPA? I do not want to use the @Query annotation. The equivalent sql query would be:

SELECT COUNT(DISTINCT rack) FROM store WHERE id = 10001;

I tried by writing the following query, but it isn't working:

int countDistinctRackById();

Ilias Mentz · Accepted Answer · 2018-10-10T15:51:33.280

8

This should work :

Integer countDistinctRackById(Long id);

edited Oct 10 '18 at 15:51

answered Jun 04 '18 at 11:06

Ilias Mentz

I've just tested that approach ant it did't work (Spring Boot 2.2.2). Please provide more info – RomanMitasov Jun 03 '20 at 12:32
can you provide more info? entity class, repository method etc? – Ilias Mentz Jun 03 '20 at 14:44
1

Sure. My repo extends from `JpaRepository`, entity class has field `customerId` and field `decision` and I need to fetch distinct count of customers for which `decision` code is one of needed values. I gave to method name `countDistinctCustomerIdByDecisionIn`, but generated SQL was not `select count(distinct CUSTOMER_ID) ...` but `select distinct count(distinct ID) ...` – RomanMitasov Jun 03 '20 at 15:35
not sure if that helps, but this looks like an issue with the `In` keyword. again I need more info to help (method's return type, entity's field type, etc) – Ilias Mentz Jun 09 '20 at 09:06
3

This method does not actually produce a distinct count of the chosen field. it returns a distinct count of the Primary keys and completely ignores the field name portion. – The Shoe Shiner Jan 29 '21 at 23:08

score 5 · Answer 2 · answered Jun 04 '18 at 11:06

5

Try this according to Spring Documentation:

Integer countDistinctRackById(String id);

Replace the word Rack with the actual name of rack field

answered Jun 04 '18 at 11:06

Afridi

score 2 · Answer 3 · answered Oct 09 '18 at 05:12

I just tried this out and none of the existing answers work.

The following query:

Integer countDistinctRackById(String id);

Will render something like this:

select distinct count(table0_.rack) as col_0_0_ from table table0_ where table0_.id=?

As I am currently stuck with Spring Boot 1.2.3, idk if there were any changes later, but to achieve a count distinct I had to define a custom query:

@Query("Select count(distinct rack) from Table t where t.id = ?1")

3 Answers3