Sort by table group count

[macphreak]

I have a table which I have grouped by the zone name. When you group by a column, it shows the "count" of the column right next to it. How do I sort by the count of the groups? (see screenshot attached and how the top 3 should be sorted at the top, etc)

[bobthebear]

Attempt 1

add a new column to your table and name it something like 'count', leave Source empty, set the type to Number, set value to 1, set Aggregation to Sum and you're good to go:

• this gets you a column with the value you're looking for, but if you expand the grouping each item will have a count of 1
• I also couldn't figure out how to get it to sort by the Aggregate value

Attempt 2

I decided to cheat. Instead of figuring out the right table settings, I modify the Data Source to add a 'group_count' property:

Table Data Source:
MY_DATA.map(item => ({...item, group_count: MY_DATA.filter(other => item.teams.sort().join(',') === other.teams.sort().join(',')).length}))

• replace MY_DATA with whatever you have set as your current Data Source.
• if you currently are using my_query1 as your data source then you would use my_query1.data or if it was my_variable you'd use my_variable.value
• you can sort this:
  • for ascending add: .sort((a, b) => a.group_count - b.group_count)
  • for descending add: .sort((a, b) => b.group_count - a.group_count)
  • if you want to use a variable to decide which way to sort add: .sort((a, b) => MY_SORT_VARIABLE.value === "asc"? a.group_count - b.group_count : b.group_count - a.group_count)
• this also lets you do this:
  
  

I haven't figured it all out as you end up with:

which ya, looks different and not so great, but you could auto expand them so it's a bit better.... otherwise this is as close as I've gotten to what you've asked so far. I don't work with tables too often so maybe somebody who has will notice what I'm missing (or have done wrong )

the code groups your array of items by their 'teams' property, it then counts how many items are in each group and it adds the value as group_count to every item.

[macphreak]

Hmmm.... tried the first one and it didn't work. I then went to the query in the transform area and replaced this:

return data

with this:

data.map(item => ({...item, group_count: data.filter(other => item.dma_name.sort().join(',') === other.dma_name.sort().join(',')).length}))

I tried to replace "dma_name" with "teams" like your sample too and it didn't work as well unfortunately.

[bobthebear]

would you mind sharing the structure of your data? I don't need values, but I do need column types:

{
  column1_name: string,
  column2_name: array[int]
}

[macphreak]

It would be the following:

{
dma_name: string, (this is what it is grouped by)
lead_name: string,
status: string,
vehicle: string,
current_bid: int (currency),
amount: int (currency),
city: string,
zip: string,
sales_name: string
}

[bobthebear]

data.map(item => ({...item, group_count: data.filter(other => item.dma_name === other.dma_name).length}))

in my example I was sorting on teams which was an array of strings ['team1', 'team2'], which is why I had .sort() and .join()... sort the list first, so order doesn't matter, then create a string from the array. no need to do that for you, you can just use the string directly =)

[WidleStudioLLP]

Hey @macphreak ,

I understand your issue. Instead of using Group By directly, you can take advantage of the Expandable Rows feature in the table. This gives you same functionality as you want—you can group your data programmatically using a transformer or JS query, and then display the grouped details inside expandable rows.

Here’s a small example I built using some mock data to match your data structure.

Sample Data

const data = [
  { dma_name: "AUSTIN", lead_name: "Lead 1", status: "Open", vehicle: "Car", current_bid: 1200, amount: 5000, city: "Austin", zip: "73301", sales_name: "John" },
  { dma_name: "AUSTIN", lead_name: "Lead 2", status: "Closed", vehicle: "Truck", current_bid: 1500, amount: 7000, city: "Austin", zip: "73301", sales_name: "Emma" },
  { dma_name: "AUSTIN", lead_name: "Lead 3", status: "Open", vehicle: "SUV", current_bid: 1800, amount: 9000, city: "Austin", zip: "73301", sales_name: "Noah" },

  { dma_name: "ABILENE-SWEETWATER", lead_name: "Lead 4", status: "Closed", vehicle: "Car", current_bid: 1000, amount: 4000, city: "Abilene", zip: "79601", sales_name: "Sophia" },
  { dma_name: "ABILENE-SWEETWATER", lead_name: "Lead 5", status: "Open", vehicle: "SUV", current_bid: 1100, amount: 4500, city: "Sweetwater", zip: "79556", sales_name: "Liam" },

  { dma_name: "ATLANTA", lead_name: "Lead 6", status: "Open", vehicle: "Truck", current_bid: 2000, amount: 8000, city: "Atlanta", zip: "30301", sales_name: "Olivia" },
  { dma_name: "ATLANTA", lead_name: "Lead 7", status: "Closed", vehicle: "Car", current_bid: 1300, amount: 6000, city: "Atlanta", zip: "30301", sales_name: "Mason" },
  { dma_name: "ATLANTA", lead_name: "Lead 8", status: "Open", vehicle: "SUV", current_bid: 1700, amount: 7500, city: "Atlanta", zip: "30301", sales_name: "Isabella" },
  { dma_name: "ATLANTA", lead_name: "Lead 9", status: "Open", vehicle: "Car", current_bid: 1400, amount: 6500, city: "Atlanta", zip: "30301", sales_name: "James" }
];

return data

Transformer for Grouping and Sorting:

// raw data (from query1 for example)
const rows = {{ transformer12.value }} ?? [];


const map = new Map();
for (const r of rows) {
  const key = r.dma_name || "—";
  if (!map.has(key)) map.set(key, { dma_name: key, count: 0, rows: [] });
  const g = map.get(key);
  g.count += 1;
  g.rows.push(r);
}

return Array.from(map.values())
  // sort by count DESC, then name ASC for ties
  .sort((a, b) => (b.count - a.count) || a.dma_name.localeCompare(b.dma_name));

This way, each dma_name becomes a parent row with a count field and its corresponding detail rows stored in rows.

Expand / Collapse Script

You can then use the following script in the table event handler to collapse all rows except the one you want expanded:

table6.collapseRows({
  mode: 'index',
  indexType: 'data',
  index: table6.data
    .map((row, i) => row.dma_name !== currentSourceRow.dma_name ? i : null)
    .filter(i => i !== null)
});

Here’s a quick screenshot from my setup for reference:

[Jack_T]

Hi @macphreak,

Was @bobthebear's updated sorting snippet or @WidleStudioLLP's approach helpful for getting your data sorted as needed?