Strapi GROUP BY and COUNT fields with the same value

Question

Is there any way in strapi to group by entries with the same fields and count its total?

Trying to create a Poll App which has "Response" Collection containing an "Answer" Field (enum: a, b, c, d). Would like to group responses with the same answers. Something like this:

{
     "answer": "a",
     "total": 3
}, {
     "answer": "b",
     "total": 1
}

Is it possible out of the box?

To give more context, here's its sql counterpart:

select *, count(answers) from responses group by answers

Answer 1

there is no known default way for groupby with entity service, however there is count query:

/src/answer/controllers/answer.js

const { createCoreController } = require("@strapi/strapi").factories;

module.exports = createCoreController("api::answer.answer", ({ strapi }) => ({
  async find(ctx) {
    let { query } = ctx;
    let answers = await strapi.db.query("api::answer.answer").findMany({
      ...query,
    });

    answers = await Promise.all(answers.map(async (answer) => ({
      ...answer,
      total: await strapi.db.query("api::answer.answer").count({where: '...'})
    })))

    return answers
  },
}));

or you can use raw query like this:

let { rows } = await strapi.db.connection.raw(
  `select id from posts where published_at IS NOT null order by random() limit ${count};
    `);

or

let { rows } = await strapi.db.connection.raw(
  `select *, count(answers) from responses group by answers;`);