Nested Query with HQL or CriteriaBuilder Query

Question

I am trying to get the result of group by query as mentioned below

Description: I have a LogDetail table and it has created_date field and UserId field. I want to get how many users get login per day. The created_date column is of DateTime type

select t, count(t) from (select substr(created_date,0,10) as t from logdetail) group by t

If I run inner query, it works fine, but when I run the nested query getting error... "unexpected token: ( near line 1, column 32"

    
     ID       User    LoginDate               LogoutDate  
     a000001  Arvind  06-05-17 12:15:00  06-05-17 12:13:00
     a000002  Murty   07-05-17 12:15:00  07-05-17 12:51:00
     a000003  John   07-05-17 12:15:00  07-05-17 12:42:00 
     a000004  John    07-05-17 02:27:00  07-05-17 03:25:00
     a000001  Tej     08-05-17 12:15:00  08-05-17 02:15:00
     a000001  John    08-05-17 12:15:00  08-05-17 01:55:00
    

Expected Result from the above table

    
     LoginDate  Frequency  
    06-05-17    01
    07-05-17    03
    08-05-17    02
    

Note: The source table has multiple entry of same user john and second point is the source date field has date-time both so first we need to get date part only from it and then apply grouping

I am looking for the solution in HQL or Criteria Builder query.

Does anyone has idea about it?

Answer 1

You can try as follows

SQL Query to get the results

Select trunc(LoginDate), count(ID) from logdetail group by trunc(LoginDate);

HQL Query for the same will be

select trunc(ld.loginDate), count(ld.Id) from logDetail ld group by trunc(ld.loginDate)

Note: On the Hql query, map as per your column mapping on the columns

Hope this solves your problem.