CODEWITHSUNDEEP
mysqllaravelgroup-byeloquentcount
Line in Linus
Line in Linus

Reputation: 420

Group by - only fetch groups with X rows

I want to fetch the book_ids for books which has > X reviews and group those to get the average rating for that book.

SELECT book_id, avg(rating) FROM `bookReviews` group by book_id` (where group count > 5)

Upvotes: 1

Views: 76

Answers (1)

Ersoy
Ersoy

Reputation: 9594

This is the query

SELECT book_id, AVG(rating) as average
FROM bookReviews
GROUP BY book_id
HAVING count(*) > 5;

This is the query builder;

return DB::table('bookReviews')
        ->select('book_id', DB::raw('AVG(rating) as average'))
        ->groupBy('book_id')
        ->having(DB::raw('count(*)'), '>', 5)
        ->get();

Upvotes: 2

Related Questions