Is it possible to use aggregate functions and property in Projections, Criteria, Grails?

Question

I am using Grails Criteria (similar to hibernate criteria) to get a list of Student who got highest grade in each division from the given table. And I want ONLY Name, Division and Grade fields.

Name | Division | Grade | Std_id
---------------------------------
AA1  | A        |  2     | 1
AA2  | A        |  4     | 2
BB1  | B        |  2     | 3
BB2  | B        |  5     | 4

The result I want is

Name | Division | Grade |
--------------------------
AA2  | A        |  4     |
BB2  | B        |  5     |

if I use the following criteria

    def criteria = Student.createCriteria()
    def resultlt = criteria.list {
        projections {
            groupProperty('divison')
            max('grade')             
        }
    }

I got ONLY Division and Grade, other fields are not included. I need Name field as well.

If I changed the criteria (used aggregate functions and property together in projections) to

    def criteria = Student.createCriteria()
    def resultlt = criteria.list {
        projections {
            property('name')
            groupProperty('divison')
            max('grade')

        }
    }

It give the following error..

ERROR: column "this_.name" must appear in the GROUP BY clause or be
 used in an aggregate function
  Position: 63. Stacktrace follows:
org.postgresql.util.PSQLException: ERROR: column "this_.name" must
appear in the GROUP BY clause or be used in an aggregate function
  Position: 63
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryEx
ecutorImpl.java:2161)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutor
Impl.java:1890)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.ja
va:255)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Stat
ement.java:559)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(Abstract
Jdbc2Statement.java:417)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc
2Statement.java:302)

score 5 · Answer 1 · answered Sep 16 '16 at 11:32

This is a common aggregation problem. the selected fields must appear in the GROUP BY clause[*]. As I can see your Division column and Name column combination are incomparable so you need to do it in another way. I think you need subquery for the above condition .

score 1 · Answer 2 · answered Sep 16 '16 at 12:43

1

I think there is no way to get answer using createCriteria but I tried something else, Please try it:

def studentList = Student.executeQuery("from Student A where A.Grade in (select max(B.Grade) from Student as B group by B.Division)")

answered Sep 16 '16 at 12:43

Prakash Bhavnath

269
2
10

the `distinct` is missing in the outer select – injecteer Sep 16 '16 at 13:23
You want all details so no need to use distinct. – Prakash Bhavnath Sep 19 '16 at 07:30

score 1 · Answer 3 · answered Sep 16 '16 at 13:33

1

You can give a try to below query.It works smoothly.

def studentDetails = Student.where {
grade == max(grade)}.property("name")).list().groupBy {"divison"}

answered Sep 16 '16 at 13:33

Abhinandan Satpute

2,558
6
25
43

score 0 · Answer 4 · answered Sep 16 '16 at 12:11

0

To avoid the problem pointed out by @Gokul you can try putting you name into a max clause:

    def resultlt = Student.withCriteria() {
        projections {
            max 'name'
            groupProperty 'divison'
            max 'grade'                  
        }
    }

Although I'm not sure about sorting here..

answered Sep 16 '16 at 12:11

injecteer

20,038
4
45
89

This solution will work only when the column you want to select is of `String` type.It will not work if the column is of `numeric(int, long, float)` type. – Abhinandan Satpute Sep 16 '16 at 12:49
why? if the aggregate function can be applied, it would work wuth any type – injecteer Sep 16 '16 at 12:50
I have just tested it out and it's giving wrong values.You can test it out as well. – Abhinandan Satpute Sep 16 '16 at 12:55
you have to be more clear. "will not work" means an error – injecteer Sep 16 '16 at 12:58
for example- If you want to select the column `age` associated with the row which has `max('grade')` and as suggested by you adding `max('age')` in the projection where the column `age` is of `numeric` type, the query will return the correct `max grade` along with wrong `age` value.It will also apply the max function on the `age` column as well. So, if `max grade is 10` and it's associated `age is 12`, and there is another row having `age value 20` which is a max age from the given table the it will return the the `correct max grade as 10` with the `wrong age value i.e 20`. – Abhinandan Satpute Sep 16 '16 at 13:14
the same happens, if agg-function is applied on `varchar` fields: you might well get strange results, but no query error. Was it your point? – injecteer Sep 16 '16 at 13:18
Yes.Absolutely. – Abhinandan Satpute Sep 16 '16 at 13:20

Is it possible to use aggregate functions and property in Projections, Criteria, Grails?

4 Answers4