Aggregate group count?

[hidden prairie]

I have created an aggregate on 'status' in my table. How do I return each 'status' and the count of records in each of those statuses?

[{status: 'Open', count: 293},{status: 'Closed', count: 384}]

currently I have below but this just returns me the count of the table.

export const countStatus = query({ handler: async (ctx) => { return await aggregateByStatus.count(ctx); }, });

below is my aggregate:

const aggregateByStatus = new TableAggregate<string, DataModel, "referrals">( components.aggregateReferralsByStatus, { sortKey: (doc) => doc.status } );

[night tiger]

Thanks for posting in #1088161997662724167.
Reminder: If you have a Convex Pro account, use the Convex Dashboard to file support tickets.

    - Provide context: What are you trying to achieve, what is the end-user interaction, what are you seeing? (full error message, command output, etc.)
    - Use [search.convex.dev](https://search.convex.dev) to search Docs, Stack, and Discord all at once.
    - Additionally, you can post your questions in the Convex Community's #1228095053885476985 channel to receive a response from AI.
    - Avoid tagging staff unless specifically instructed.

    Thank you!

[lusty gyro]

You can get the set of distinct statuses like this https://stack.convex.dev/select-distinct (or if you have a static list of statuses, use that). then pass each status into the bounds of aggregate.count(ctx, bounds)

[hidden prairie]

Thanks @Lee, I use the 'Distinct without SQL' to get a DISTINCT list of a field in my table, I couldn't workout how to get the count as well, can you let me know how to do that? I'm not sure what the bounds should be for the other way.

`export const getDistinctValues = query({
args: {
field: v.string(),
},
handler: async (ctx, args) => {
const distinctValues = new Set();
let indexToUse:
| "by_adviser_company"
| "by_adviser_nameWithoutTitle"
| "by_status";
if (args.field === "adviser.company") {
indexToUse = "by_adviser_company";
} else if (args.field === "adviser.nameWithoutTitle") {
indexToUse = "by_adviser_nameWithoutTitle";
} else {
indexToUse = "by_status";
}

let doc = await ctx.db
  .query("referrals")
  .withIndex(indexToUse)
  .order("desc")
  .first();

while (doc !== null) {
  // Extract nested value
  let value = doc as any;
  if (args.field === "adviser.company") {
    value = value?.adviser?.company;
  } else if (args.field === "adviser.nameWithoutTitle") {
    value = value?.adviser?.nameWithoutTitle;
  } else {
    value = value?.status;
  }

  if (value !== undefined && value !== null) {
    distinctValues.add(value);
  }

  // Extract nested value like we did above
  let fieldValue;
  if (args.field === "adviser.company") {
    fieldValue = doc.adviser?.company;
  } else if (args.field === "adviser.nameWithoutTitle") {
    fieldValue = doc.adviser?.nameWithoutTitle;
  } else {
    fieldValue = doc.status;
  }

  doc = await ctx.db
    .query("referrals")
    .withIndex(indexToUse, (q) =>
      q.lt(args.field as any, fieldValue as any)
    )
    .order("desc")
    .first();
}

return Array.from(distinctValues).sort();

},
});`

[lusty gyro]

there are two steps here:

1. do the SELECT DISTINCT pattern to get all of the distinct fields
2. use the aggregate that you already have, on each distinct value like

for (const distinctValue of distinctValues) {
  const countForValue = await aggregateByStatus.count(ctx, { upper: { key: distinctValue, inclusive: true }, lower: { key: distinctValue, inclusive: true } });
}