How to GROUP BY whole table object in Oracle and JPA?-CodePudding

I'm struggling with incompatible SQL statement between PostgreSQL and Oracle.

I made SELECT statement for PostgreSQL like below:

SELECT usr AS user, COUNT(bk) AS count
FROM user usr
    LEFT JOIN book bk ON usr.id = bk.user
GROUP BY usr;

This statement can be run on PostgreSQL DB without any problems. Unfortunately not in Oracle DB. I tried with multiple another statements, ex. select usr.* etc. but in Oracle it does not work.

It tells me every time that column usr cannot be resolved.

Is there any other way to select whole object (which could be correctly mapped by Hibernate later) together with group by? And is it possible to make this statement compatible with those 2 DB?

I'm looking for the approachment, which I've already made for Hibernate with PostgreSQL, so that statement would give me Tuple<User, Long>.

CodePudding user response：

If you want to get the count of books per user, don't group the result of the join, but join to the result of a GROUP BY:

SELECT usr.*, coalesce(b.num_books, 0) as num_books
FROM user usr
  LEFT JOIN ( 
    select bk.user, count(*) as num_books
    from book bk
    group by bk.user
  ) b ON usr.id = b.user

That would be more efficient in Postgres as well.

CodePudding user response：

SELECT usr., coealesce(b.num_books, 0) as num_books FROM user usr LEFT JOIN ( select bk.user, count() as num_books from book bk group by bk.user ) b ON usr.id = b.user