# Subtotal for Second Level Grouping

**URL:** <https://forums.knack.com/t/subtotal-for-second-level-grouping/18237>\
**Category:** Ask Developers\
**Created:** [November 25, 2024, 12:07am UTC](https://forums.knack.com/t/subtotal-for-second-level-grouping/18237 "2024-11-25T00:07:03Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![TootsieTor35028](https://sea2.discourse-cdn.com/flex020/user_avatar/forums.knack.com/tootsietor35028/32/4474_2.png) [@TootsieTor35028](https://forums.knack.com/u/TootsieTor35028)\
**Post date:** [November 25, 2024, 12:07am UTC](https://forums.knack.com/t/subtotal-for-second-level-grouping/18237/1 "2024-11-25T00:07:03Z")

</div>

Like many others before me, I would like to have a subtotal for groupings beyond level 1 in a grid. I have been fiddling around with code for a few days, trying to piecemeal bits of code together into solution. I am not fluent enough in Javascript though and I am really not making any progress. Here’s what I was trying to achieve:

1. For each sublevel, pull all the values from a particular field and sum them into a var.
2. Prepend a table row before the next sublevel grouping:  
a. New TR must have same number of TD as all other TR  
b. Sum of field should go in the correct corresponding TD in the new TR
3. Repeat for all sublevels

Anyone willing to help me out with any of these items? Or has anyone made a similar solution already that I haven’t found?

---

<div class="post-metadata">

**Author:** ![Erik](https://sea2.discourse-cdn.com/flex020/user_avatar/forums.knack.com/erik/32/982_2.png) [@Erik](https://forums.knack.com/u/Erik)\
**Post date:** [November 25, 2024, 2:48pm UTC](https://forums.knack.com/t/subtotal-for-second-level-grouping/18237/2 "2024-11-25T14:48:00Z")

</div>

Following…

---

<div class="post-metadata">

**Author:** ![StephenChapman](https://sea2.discourse-cdn.com/flex020/user_avatar/forums.knack.com/stephenchapman/32/1759_2.png) [@StephenChapman](https://forums.knack.com/u/StephenChapman)\
**Post date:** [November 25, 2024, 10:53pm UTC](https://forums.knack.com/t/subtotal-for-second-level-grouping/18237/3 "2024-11-25T22:53:20Z")

</div>

@TootsieTor35028 Ooh I love a good coding challenge, and I can see this is something that has been requested a lot in the forums.

This video will provide a demonstration of how this solution works, as well as a walkthrough of what the below code does:

> **[2024-11-26, Knack, Group Calculations.mp4](https://drive.google.com/file/d/124BDnjBDM4iTbyyTqBe8xGQk27RD49M-/view?usp=drive_fs)**
>
> Google Drive file.

 ![image](https://us1.discourse-cdn.com/flex020/uploads/knack/original/2X/b/bb58afe1f5018e9859b7390be8cf1bdc87893aa0.png)

**Prerequisites:**

- A fair understanding of using JavaScript in Knack
- A grid view (`view_xx`) that has:
  - One or more groupings
  - One or more number/equation/currency fields that you want to calculate (`field_xx`, `field_yy`)
  - ZERO column summaries

**Compatibility**  
This code conflicts with the [KTL library](https://github.com/cortexrd/Knack-Toolkit-Library/tree/prod?tab=readme-ov-file) when removing the `colspan` value of grouped rows, as KTL automatically reapplies the `colspan` for grid views. If you want to utilise both, you’ll need to insert a `MutationObserver` that prevents this being reapplied.

**Method:**

1. Insert the following code into your Knack JavaScript editor
2. Replace `view_xx`, `field_xx`, and `field_yy` with your own view and field keys  
Note that your calcFields MUST be number, equation, or currency fields
3. Update the calcField item attributes depending on how you want to output the calculated value

```jsx
$(document).on('knack-records-render.view_xx', async function(event, view, records){

  calcFields = [
    { key: 'field_xx', calc: 'sum', comma: true, decimals: 2, pre: '$', post: '' },
    { key: 'field_yy', calc: 'sum', comma: true, decimals: 2, pre: '$', post: '' }
  ]
  addGroupCalculations(view, calcFields);

});

function addGroupCalculations(view, calcFields) {

  // Determine how to source the column structure based on the view type
  const viewType = Knack.models[view.key].view.type;
  viewColumns = {
    table: Knack.models[view.key]?.view?.columns,
    search: Knack.models[view.key]?.view?.results?.columns
  }

  // If the view type is not table or search, then throw an error
  if (!viewColumns[viewType]) {
    throw new Error('Invalid view type. Must be a table or search type view');
  }

  const columns = viewColumns[viewType];
  console.log('columns',columns)

  $(`#${view.key} .kn-table tbody`).prepend(`<tr class="kn-table-group kn-group-level-0"><td>Total</td></tr>`); // Add totals row to top of grid
  
  // For each group row...
  $(`#${view.key} .kn-table tbody .kn-table-group`).each(function() {
    const $row = $(this);
    const level = $row.attr('class').match(/kn-group-level-(\d)/); // Get the group level
    
    $(this).find('td').removeAttr('colspan'); // Remove the default colspan for group rows

    // Add table cells on group rows
    columns.filter(column => column.grouping === false).slice(1).forEach(column => { // Ignore the first column, as it's reserved for the group title
      const fieldKey = column?.id || ''; // Get the field key for each column if it exists
      $row.append($(`<td style="text-align:${column.align}" class="${fieldKey}"></td>`)); // Add new row cells with their respective field key
    });

    // For each calcField array item...
    calcFields.forEach(function(field) {
      const value = calcValues($row.nextUntil(`.${level[0]}`), field); // Sum all values up until the next same group level in the grid
      const formattedValue = formatValue(value, field); // Format the value based on the specified attributes
      $row.find(`.${field.key}`).text(formattedValue); // Add the value to the group row's cell
    });

  });

  $(`#${view.key} .kn-table tbody`).append($(`#${view.key} .kn-table tbody .kn-group-level-0`)); // Move the total row to the end of the grid
      
}

// Calculates all found field values based on the chosen calculation and returns the calculated value (i.e. sum, average, min, or max)
function calcValues(elements, field) {
  const values = elements.find(`td.${field.key}`).map(function() {
    return parseFloat($(this).text().replace(/[^\d.-]/g, '')) || 0;
  }).get();
  switch (field.calc) {
  case 'sum':
    return values.reduce((total, num) => total + num, 0);
  case 'average':
    return values.reduce((total, num) => total + num, 0) / values.length;
  case 'min':
    return Math.min(...values);
  case 'max':
    return Math.max(...values);
  default:
    throw new Error('Invalid calculation type. Use sum, average, min, or max.');
  }
}

// Formats a value based on the attributes that are loaded in
function formatValue(value, field) {
  const comma = field?.comma || false;
  const decimals = field?.decimals || 0;
  const pre = field?.pre || '';
  const post = field?.post || '';
  let newValue = value;
  newValue = newValue.toFixed(decimals);
  newValue = comma ? Knack.formatNumberWithCommas(newValue) : newValue;
  newValue = pre + newValue + post;
  return newValue;
}

```

Please feel free to share your feedback, or [buy me a coffee](https://buymeacoffee.com/stephenchapman) if this has been valuable to you.  
If you need help installing/modifying this, please contact me privately.

---

<div class="post-metadata">

**Author:** ![CarlHolmes](https://sea2.discourse-cdn.com/flex020/user_avatar/forums.knack.com/carlholmes/32/2814_2.png) [@CarlHolmes](https://forums.knack.com/u/CarlHolmes)\
**Post date:** [November 26, 2024, 12:12pm UTC](https://forums.knack.com/t/subtotal-for-second-level-grouping/18237/4 "2024-11-26T12:12:58Z")

</div>

Mind officially blown 🧠 💥👏👏👏

---

<div class="post-metadata">

**Author:** ![TootsieTor35028](https://sea2.discourse-cdn.com/flex020/user_avatar/forums.knack.com/tootsietor35028/32/4474_2.png) [@TootsieTor35028](https://forums.knack.com/u/TootsieTor35028)\
**Post date:** [November 26, 2024, 5:29pm UTC](https://forums.knack.com/t/subtotal-for-second-level-grouping/18237/5 "2024-11-26T17:29:33Z")

</div>

This is amazing; thank you! I will give it a try today. ChatGPT and I have been putting something together that sort of works, but is no where near as elegant as your solution.

Here is a screenshot of where I was at with it. This morning I started getting double subtotals and the formatting is still lacking.

 ![Screenshot 2024-11-26 at 9.32.19 AM](https://us1.discourse-cdn.com/flex020/uploads/knack/original/2X/0/0155ea3e80e7acfac25325cbc31093e2ff291801.png)

---

<div class="post-metadata">

**Author:** ![TootsieTor35028](https://sea2.discourse-cdn.com/flex020/user_avatar/forums.knack.com/tootsietor35028/32/4474_2.png) [@TootsieTor35028](https://forums.knack.com/u/TootsieTor35028)\
**Post date:** [November 26, 2024, 5:53pm UTC](https://forums.knack.com/t/subtotal-for-second-level-grouping/18237/6 "2024-11-26T17:53:47Z")

</div>

@StephenChapman I dropped your code into my project and it is almost perfect. In my case, I am summing 4 columns, starting in column 3. It looks like the code did not actually remove the “colspan” from the td for the group row, which result in the summaries going off the table in extra cells.

 ![Screenshot 2024-11-26 at 9.46.00 AM](https://us1.discourse-cdn.com/flex020/uploads/knack/original/2X/b/b261421b72087eb3b8988b38fee5c62434bffa5a.png)

Also, for some reason, I got duplicate summaries, so 8 new cells instead of 4.

 ![Screenshot 2024-11-26 at 9.52.49 AM](https://us1.discourse-cdn.com/flex020/uploads/knack/original/2X/3/3359464c5f3f4e86d7c289795186333c9e793f79.png)

---

<div class="post-metadata">

**Author:** ![StephenChapman](https://sea2.discourse-cdn.com/flex020/user_avatar/forums.knack.com/stephenchapman/32/1759_2.png) [@StephenChapman](https://forums.knack.com/u/StephenChapman)\
**Post date:** [November 26, 2024, 7:56pm UTC](https://forums.knack.com/t/subtotal-for-second-level-grouping/18237/7 "2024-11-26T19:56:23Z")

</div>

Thanks @TootsieTor35028, I’ve messaged you privately to try and help diagnose.

---

<div class="post-metadata">

**Author:** ![StephenChapman](https://sea2.discourse-cdn.com/flex020/user_avatar/forums.knack.com/stephenchapman/32/1759_2.png) [@StephenChapman](https://forums.knack.com/u/StephenChapman)\
**Post date:** [November 26, 2024, 8:54pm UTC](https://forums.knack.com/t/subtotal-for-second-level-grouping/18237/8 "2024-11-26T20:54:58Z")

</div>

Oops, I didn’t account for search views in my testing! Updated code now with following changes:

- Updated handler to `records-render` instead of `view-render`
- Added logic to determine column structure based on view type, as it differs between table (grid) and search.

EDIT: Turns out that this conflicts with the [KTL library](https://github.com/cortexrd/Knack-Toolkit-Library/tree/prod?tab=readme-ov-file) when removing the `colspan` value of grouped rows, as KTL automatically reapplies the `colspan` for any grid/search operations. If you want to utilise both, you’ll need to insert a `MutationObserver` that prevents this being reapplied.

---

<div class="post-metadata">

**Author:** ![shipping](https://avatars.discourse-cdn.com/v4/letter/s/ed655f/32.png) [@shipping](https://forums.knack.com/u/shipping)\
**Post date:** [December 26, 2024, 6:23pm UTC](https://forums.knack.com/t/subtotal-for-second-level-grouping/18237/9 "2024-12-26T18:23:54Z")

</div>

This is amazing! Thank you Stephen!  
But as they say, much wants more. Would it be possible to have a piece of code that would only show the grouping header when there is more than one table row in that grouping? In your example, under “Commercial” there is only one entry, so the entry and the sum are the same number. I have a table with mostly single entries, so not having those single entries grouped would clean things up quite a bit.

If I would have this option, I would then include the grouped field twice, so that it could be used once for grouping, and also as the first column to identify the ungrouped single entries.

Thanks in advance and enjoy your [coffee](https://buymeacoffee.com/stephenchapman)!

---

<div class="post-metadata">

**Author:** ![shipping](https://avatars.discourse-cdn.com/v4/letter/s/ed655f/32.png) [@shipping](https://forums.knack.com/u/shipping)\
**Post date:** [December 26, 2024, 9:09pm UTC](https://forums.knack.com/t/subtotal-for-second-level-grouping/18237/10 "2024-12-26T21:09:09Z")

</div>

The problem with search views might be similar to the problem I’m having when I set the table to “Hide empty columns” and hide two empty columns (see photo). The trouble is, I’m a copy and paster, not a coder, so you will have show me how to use the MutationObserver.

 ![Screenshot 2024-12-26 161037](https://us1.discourse-cdn.com/flex020/uploads/knack/original/2X/0/0f9b2cc6e9d0b3ab39e46306fd2b8b69316ede0d.png)

---

<div class="post-metadata">

**Author:** ![StephenChapman](https://sea2.discourse-cdn.com/flex020/user_avatar/forums.knack.com/stephenchapman/32/1759_2.png) [@StephenChapman](https://forums.knack.com/u/StephenChapman)\
**Post date:** [December 26, 2024, 10:54pm UTC](https://forums.knack.com/t/subtotal-for-second-level-grouping/18237/11 "2024-12-26T22:54:26Z")

</div>

@shipping Thanks for the coffee, much appreciated!

I can’t edit my original code unfortunately, but here is a fixed, refactored version to cater for hidden columns.  
Beforehand I was fetching the column structure in a way that couldn’t detect that a column was hidden.

```jsx
$(document).on('knack-records-render.view_xx', async function(event, view, records){

  const calcFields = [
    { key: 'field_xx', calc: 'min', comma: true, decimals: 2, pre: '$', post: '' },
    { key: 'field_yyy', calc: 'sum', comma: true, decimals: 2, pre: '$', post: '' },
  ]
  
  addGroupCalculations(view, calcFields);

});

// Function to add calculations to grouped table rows
function addGroupCalculations(view, calcFields) {

  const $tbody = $(`#${view.key} .kn-table tbody`);

  if ($tbody.find('.kn-tr-nodata').length > 0) return; // Exit the function if there is no data

  const tableCells = $tbody.find('tr:not([class*="kn-table-group"])').first().find('td:gt(0)').clone().empty(); // Get table cell structure from first available row, ignoring the first cell, as it's reserved for the group title
  
  $tbody.prepend(`<tr class="kn-table-group kn-group-level-0"><td>Total</td></tr>`); // Add totals row to top of grid

  // For each group row...
  $tbody.find('.kn-table-group').each(function() {
    const $row = $(this);
    const level = $row.attr('class').match(/kn-group-level-(\d)/); // Get the group level
    
    $row.find('td').removeAttr('colspan'); // Remove the default colspan for group rows
    
    $row.append(tableCells.clone()); // Add table cells to group row

    // For each calcField array item...
    calcFields.forEach(function(field) {
      const value = calcValues($row.nextUntil(`.${level[0]}`), field); // Sum all values up until the next same group level in the grid
      const formattedValue = formatValue(value, field); // Format the value based on the specified attributes
      $row.find(`.${field.key}`).text(formattedValue); // Add the value to the group row's cell
    });

  });

  $tbody.append($tbody.find('.kn-group-level-0')); // Move the total row to the end of the grid
      
}

// Calculates all found field values based on the chosen calculation and returns the calculated value (i.e. sum, average, min, or max)
function calcValues(elements, field) {
  const values = elements.find(`td.${field.key}`).map(function() {
    return parseFloat($(this).text().replace(/[^\d.-]/g, '')) || 0;
  }).get();
  switch (field.calc) {
  case 'sum':
    return values.reduce((total, num) => total + num, 0);
  case 'average':
    return values.reduce((total, num) => total + num, 0) / values.length;
  case 'min':
    return Math.min(...values);
  case 'max':
    return Math.max(...values);
  default:
    throw new Error('Invalid calculation type. Use sum, average, min, or max.');
  }
}

// Formats a value based on the attributes that are loaded in
function formatValue(value, field) {
  const comma = field?.comma || false;
  const decimals = field?.decimals || 0;
  const pre = field?.pre || '';
  const post = field?.post || '';
  let newValue = value;
  newValue = newValue.toFixed(decimals);
  newValue = comma ? Knack.formatNumberWithCommas(newValue) : newValue;
  newValue = pre + newValue + post;
  return newValue;
}

```

---

<div class="post-metadata">

**Author:** ![StephenChapman](https://sea2.discourse-cdn.com/flex020/user_avatar/forums.knack.com/stephenchapman/32/1759_2.png) [@StephenChapman](https://forums.knack.com/u/StephenChapman)\
**Post date:** [December 26, 2024, 10:58pm UTC](https://forums.knack.com/t/subtotal-for-second-level-grouping/18237/12 "2024-12-26T22:58:10Z")

</div>

Regarding the ‘only one row in the grouping’ solution, it gets a little tricky when dealing with multiple groupings, so I have not pursued this for the moment.  
If you’d like my help to build a solution for this, feel free to message me privately.  
Thanks!

---

<div class="post-metadata">

**Author:** ![Tim5](https://avatars.discourse-cdn.com/v4/letter/t/e9c0ed/32.png) [@Tim5](https://forums.knack.com/u/Tim5)\
**Post date:** [April 6, 2025, 11:45am UTC](https://forums.knack.com/t/subtotal-for-second-level-grouping/18237/13 "2025-04-06T11:45:10Z")

</div>

This is really helpful. Thanks a bunch. If you want to eliminate the larger total from the grid altogether, you can add the following code:

if (level[0] == ‘kn-group-level-1’) return;

and then comment out:

$(`#${view.key} .kn-table tbody`).prepend(`<tr class="kn-table-group kn-group-level-0"><td>Total</td></tr>`); // Add totals row to top of grid

You can also customize the colors on any level by using the following CSS (reference your own table and view)

/\* Makes level one on Award by Group table the colors below\*/

#view\_1255 .kn-table-group.kn-group-level-1 {  
background-color: black !important;  
color: white; /\* Optional: makes text readable\*/  
}

 ![Screenshot 2025-04-06 074357](https://us1.discourse-cdn.com/flex020/uploads/knack/original/2X/2/2756e6e5236de5ec12e3abccfca9737e15dbe2ef.png)

 ![Screenshot 2025-04-06 073801](https://us1.discourse-cdn.com/flex020/uploads/knack/original/2X/a/a01323031be782361199771c9d6311c44cda28f2.png)

---

<div class="post-metadata">

**Author:** ![Phillip2](https://sea2.discourse-cdn.com/flex020/user_avatar/forums.knack.com/phillip2/32/5528_2.png) [@Phillip2](https://forums.knack.com/u/Phillip2)\
**Post date:** [June 3, 2026, 1:41pm UTC](https://forums.knack.com/t/subtotal-for-second-level-grouping/18237/14 "2026-06-03T13:41:05Z")

</div>

Hi Stephen!

This code is still one of our most useful features to summarize grids. I have a new request. How hard would it be to add a count calculation that works on all fields, not just number/equation/currency? That way in addition to having a sum field above our number column, we could also have an “n=” field above the text description column.

I’m a faithful client of yours, so don’t hesitate to reach out privately if you prefer.

---

<div class="post-metadata">

**Author:** ![StephenChapman](https://sea2.discourse-cdn.com/flex020/user_avatar/forums.knack.com/stephenchapman/32/1759_2.png) [@StephenChapman](https://forums.knack.com/u/StephenChapman)\
**Post date:** [June 3, 2026, 4:43pm UTC](https://forums.knack.com/t/subtotal-for-second-level-grouping/18237/15 "2026-06-03T16:43:56Z")

</div>

Hey Phil, glad to hear it! I’ll check it out when I return from overseas! That shouldn’t be an issue.