Spring Data JPA - Custom Query with multiple aggregate functions in result

Question

I was trying to return an average and count of a set of ratings in one query. I managed it fairly easily in two queries following the example I found browsing. For example:

@Query("SELECT AVG(rating) from UserVideoRating where videoId=:videoId")
public double findAverageByVideoId(@Param("videoId") long videoId);

but as soon as I wanted an average and a count in the same query, the trouble started. After many hours experimenting, I found this worked, so I am sharing it here. I hope it helps.

1) I needed a new class for the results:

The I had to reference that class in the query:

@Query("SELECT new org.magnum.mobilecloud.video.model.AggregateResults(AVG(rating) as rating, COUNT(rating) as TotalRatings) from UserVideoRating where videoId=:videoId")
public AggregateResults findAvgRatingByVideoId(@Param("videoId") long videoId);

One query now returns average rating and count of ratings

does this populate other attribites as well. – Jef Dec 02 '21 at 21:20 — Jef, Dec 02 '21 at 21:20

score 22 · Answer 1 · edited Dec 09 '18 at 19:04

22

Solved myself.

Custom class to receive results:

public class AggregateResults {

    private final double rating;
    private final int totalRatings;

    public AggregateResults(double rating, long totalRatings) {
        this.rating = rating;
        this.totalRatings = (int) totalRatings;
    }

    public double getRating() {
        return rating;
    }

    public int getTotalRatings() {
        return totalRatings;
    }
}

and

@Query("SELECT new org.magnum.mobilecloud.video.model.AggregateResults(
    AVG(rating) as rating, 
    COUNT(rating) as TotalRatings) 
    FROM UserVideoRating
    WHERE videoId=:videoId")
public AggregateResults findAvgRatingByVideoId(@Param("videoId") long videoId);

edited Dec 09 '18 at 19:04

Anton Hlinisty

1,441
1
20
35

answered Aug 17 '15 at 11:16

formica

934
1
8
16

Please define your custom class – Garry Aug 17 '15 at 11:22
public class AggregateResults { private final double rating; private final int totalRatings; public AggregateResults(double rating, long totalRatings) { this.rating = rating; this.totalRatings = (int) totalRatings; } public double getRating() { return rating; } public int getTotalRatings() { return totalRatings; } } – formica Aug 17 '15 at 13:21
thank you...Please add this to your answer and relate – Garry Aug 17 '15 at 13:22
Is your custom class marked as "Entity"? I've got error "entity has no primary key attribute defined" – jmhostalet Jul 31 '16 at 06:30
@jmhostalet You need (at)Entity against the classes that map to tables, yes, but not the custom result class. Your error sounds like an issue with the class mapped to the db table. You need to have a column tagged with (at)Id somewhere in that class. For example: (at)Id (at)GeneratedValue(strategy = GenerationType.AUTO) private long id; Sorry the dialogue won't let me enter "at" signs – formica Aug 03 '16 at 22:16
I have manyToOne relationship from my domain(modal) class to another domain class. I want to add that object also in group by and in select query. Can anyone please help? – Shamseer Aug 26 '16 at 12:22

score 4 · Answer 2 · answered Jan 01 '17 at 21:12

Thanks.

You should prevent NPEs and hibernate parsing tuple errors as following :

public class AggregateResults {

private final double rating;
private final int totalRatings;

public AggregateResults(Double rating, Long totalRatings) {
    this.rating = rating == null ? 0 : rating;
    this.totalRatings = totalRatings == null ? 0 : totalRatings.intValue();
}

public double getRating() {
    return rating;
}
public int getTotalRatings() {
    return totalRatings;
}}

Yes, I guess the AVG() could return null although I think the COUNT() would be 0, if no rows matched. — formica, Jan 12 '17 at 15:04

Spring Data JPA - Custom Query with multiple aggregate functions in result

2 Answers2

Linked