sanitygroq

How to use arithmetic operator to calculate sum


I have a review type with a rating field that contains a number from 1 to 5. Now I'd like to calculate the average rating. Relevant schema:

    {
      name: 'rating',
      title: 'Rating',
      validation: Rule => Rule.required(),
      type: 'number'
    },

Sanity recently added arithmetic operations to GROQ, but I'm not sure how to use them and there's very little documentation.

  1. Is it possible to calculate the sum of all ratings using an arithmetic operation? If so, how?
  2. Can you also divide the sum by the number of all reviews in the same query?

Solution

  • I found a solution, quite simple really. You count each star individually and then sum them up and divide them by the total count.

    "rating":
      (
        (count(*[_type=='review' && references(^._id) && rating == 1]) * 1) +
        (count(*[_type=='review' && references(^._id) && rating == 2]) * 2) +
        (count(*[_type=='review' && references(^._id) && rating == 3]) * 3) +
        (count(*[_type=='review' && references(^._id) && rating == 4]) * 4) +
        (count(*[_type=='review' && references(^._id) && rating == 5]) * 5)
      ) / count(*[_type=='review' && references(^._id)])
    

    I'd imagine that this is somewhat expensive for Sanity to calculate, and it's a bit verbose, so I'm still interested in other solutions.