Group by and having clauses in HQL

In the previous example we saw the aggregate Functions supported in HQL. But reporting is also dependent on grouping. Examples would be max scores of students grouped by their class, most expensive items sold in a shop grouped by material etc.
I decided to get the total ids entities associated with each EntityMaster instance in the database.

public static void testSimpleGroupBy() {
        final Session session = sessionFactory.openSession();
        Query q = session.createQuery("select count(e), e.master.id from " +
                "Entity e group by e.master.id");
        List<Object[]> recs = q.list();
        for (Object[] line : recs) {
            System.out.println("Total entities under Master with id " + line[1] + " is " + line[0]);
        }
    }

The output indicates the number of entities associated with each Master:

Hibernate: 
    /* select
        count(e),
        e.master.id 
    from
        Entity e 
    group by
        e.master.id */ 
        select
            count(entity0_.ID) as col_0_0_,
            entity0_.MASTER_ID as col_1_0_ 
        from
            ENTITY entity0_ 
        group by
            entity0_.MASTER_ID
Total entities under Master with id 1 is 2
Total entities under Master with id 2 is 2

As can be seen in the above HQL query, the select clause involved two projections - one was the count() function and the other was the master.id field. The same master.id field appears in the Group by clause. This is same like SQL - Other than aggregate functions no property can be projected in the select clause unless it is a part of the group by clause. 
While we have done the grouping we may like to add some restrictions on the groupings returned. This can be achieved using the having clause. For example I would like to restrict the above results to master with id 1.

public static void testSimpleGroupBy1() {
    final Session session = sessionFactory.openSession();
    Query q = session.createQuery("select count(e), e.master.id from " +
            "Entity e group by e.master.id having e.master.id =1 ");
    List<Object[]> recs = q.list();
    for (Object[] line : recs) {
        System.out.println("Total entities under Master with id " + line[1] + " is " + line[0]);
    }
}

The output is :

        select
            count(entity0_.ID) as col_0_0_,
            entity0_.MASTER_ID as col_1_0_ 
        from
            ENTITY entity0_ 
        group by
            entity0_.MASTER_ID 
        having
            entity0_.MASTER_ID=1
Total entities under Master with id 1 is 2

The same rule applies for Having clause as it does for select clause. Only aggregate functions and fields that feature in group by can appear here.

However the same output could also be obtained using a where clause:

public static void testSimpleGroupBy2() {
    final Session session = sessionFactory.openSession();
    Query q = session.createQuery("select count(e), e.master.id from " +
            "Entity e where e.master.id = 1 group by e.master.id");
    List<Object[]> recs = q.list();
    for (Object[] line : recs) {
        System.out.println("Total entities under Master with id " + line[1] + " is " + line[0]);
    }
}

Output:

        select
            count(entity0_.ID) as col_0_0_,
            entity0_.MASTER_ID as col_1_0_ 
        from
            ENTITY entity0_ 
        where
            entity0_.MASTER_ID=1 
        group by
            entity0_.MASTER_ID
Total entities under Master with id 1 is 2

So why use the having clause ? The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. There is a very good link that gives an example illustrating this point. Accordingly I changed the code to display child count for Masters who are associated with at least 5 entities.

public static void testGroupByHaving() {
    final Session session = sessionFactory.openSession();
    Query q = session.createQuery("select count(e), e.master.id from " +
            "Entity e group by e.master.id having count(e) > 4 ");
    List<Object[]> recs = q.list();
    for (Object[] line : recs) {
        System.out.println("Total entities under Master with id " + line[1] + " is " + line[0]);
    }
}

The generated query is :

        select
            count(entity0_.ID) as col_0_0_,
            entity0_.MASTER_ID as col_1_0_ 
        from
            ENTITY entity0_ 
        group by
            entity0_.MASTER_ID 
        having
            count(entity0_.ID)>4

Adding this condition in a where clause will result in an error.