'Spring Boot native query gives error when counting distinct records referenced by a key

I have 2 tables: book_store and book. book table has store_id which reference to book_store.

When I run this query on a regular query client, it works fine.

select 
    B.id                                as storeId,
    B.name                              as storeName,
    (select count(distinct book_name) 
       from book where store_id = B.id) as distinctBookCount
FROM book_store B;

However, using spring boot jpa native query, it fails. The error is just

Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "where"

Is there a possibility that JPA native query does not support such queries?

javapostgresqlspring-bootjpaspring-data-jpa


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions