Javascript group by 2 columns and sum up total

Question

I found a similar problem to mine on above post.

Need to get the total from the below json

const d = [ 
    { Phase: "Phase 1", Step: "Step 1", Task: "Task 1", Value: "5" },
    { Phase: "Phase 1", Step: "Step 1", Task: "Task 2", Value: "10" },
    { Phase: "Phase 1", Step: "Step 2", Task: "Task 1", Value: "15" },
    { Phase: "Phase 1", Step: "Step 2", Task: "Task 2", Value: "20" },
    { Phase: "Phase 2", Step: "Step 1", Task: "Task 1", Value: "25" },
    { Phase: "Phase 2", Step: "Step 1", Task: "Task 2", Value: "30" },
    { Phase: "Phase 2", Step: "Step 2", Task: "Task 1", Value: "35" },
    { Phase: "Phase 2", Step: "Step 2", Task: "Task 2", Value: "40" }
];

I used the solution as given in the answers

function groupBy(data, fields, sumBy='Value') {
  let r=[], cmp= (x,y) => fields.reduce((a,b)=> a && x[b]==y[b], true);
  data.forEach(x=> {
    let y=r.find(z=>cmp(x,z));
    let w= [...fields,sumBy].reduce((a,b) => (a[b]=x[b],a), {})
    y ? y[sumBy]=+y[sumBy]+(+x[sumBy]) : r.push(w);
  });
  return r;
}

However I need to achieve the below result for my purpose. Not sure how to achieve this.

{ Phase: "Phase 1", Step: "Step 1", Task: "Task 1, Task 2", Value: "15" },
    { Phase: "Phase 1", Step: "Step 2", Task: "Task 1, Task 2", Value: "35" },    
    { Phase: "Phase 2", Step: "Step 1", Task: "Task 1, Task 2", Value: "55" },    
    { Phase: "Phase 2", Step: "Step 2", Task: "Task 1, Task 2", Value: "75" }

please add the data in a valid javascript form and add your try. — Nina Scholz, Jan 18 '21 at 17:41
Please [edit] your question to show what research you've done, and any attempts you've made based on that research. — Heretic Monkey, Jan 18 '21 at 17:43

Hassan Imam · Answer 1 · 2021-01-22T04:30:28.497

You can use an object accumulator to group your array based on Phase and Step and sum up Value for same key.

const data = [ { Phase: "Phase 1", Step: "Step 1", Task: "Task 1", Value: "5" },  { Phase: "Phase 1", Step: "Step 1", Task: "Task 2", Value: "10" }, { Phase: "Phase 1", Step: "Step 2", Task: "Task 1", Value: "15" }, { Phase: "Phase 1", Step: "Step 2", Task: "Task 2", Value: "20" }, { Phase: "Phase 2", Step: "Step 1", Task: "Task 1", Value: "25" }, { Phase: "Phase 2", Step: "Step 1", Task: "Task 2", Value: "30" }, { Phase: "Phase 2", Step: "Step 2", Task: "Task 1", Value: "35" }, { Phase: "Phase 2", Step: "Step 2", Task: "Task 2", Value: "40" } ],
      result = Object.values(data.reduce((r,o) => {
        const key = `${o.Phase}_${o.Step}`;
        r[key] = r[key] || {Phase: o.Phase, Step: o.Step, Task: [], Value: 0};
        r[key].Task.push(o.Task);
        r[key].Value += +o.Value;
        return r;
      }, {}))
      .map(o => ({...o, Task: o.Task.join(',')}));
console.log(result);

.as-console-wrapper { max-height: 100% !important; top: 0; }

You can create a dynamic keys based on the Key name in Keys array and group your data array based on this key.

const data = [ { Phase: "Phase 1", Step: "Step 1", Task: "Task 1", Value: "5" },  { Phase: "Phase 1", Step: "Step 1", Task: "Task 2", Value: "10" }, { Phase: "Phase 1", Step: "Step 2", Task: "Task 1", Value: "15" }, { Phase: "Phase 1", Step: "Step 2", Task: "Task 2", Value: "20" }, { Phase: "Phase 2", Step: "Step 1", Task: "Task 1", Value: "25" }, { Phase: "Phase 2", Step: "Step 1", Task: "Task 2", Value: "30" }, { Phase: "Phase 2", Step: "Step 2", Task: "Task 1", Value: "35" }, { Phase: "Phase 2", Step: "Step 2", Task: "Task 2", Value: "40" } ],
      keys = ['Phase', 'Step'],
      result = Object.values(data.reduce((r,o) => {
        const key = keys.map(k => o[k]).join('_');
        r[key] = r[key] || keys.reduce((ob, k) =>  ({...ob, [k]: o[k]}), {Task: [], Value: 0});
        r[key].Task.push(o.Task);
        r[key].Value += +o.Value;
        return r;
      }, {}))
      .map(o => ({...o, Task: o.Task.join(',')}));
console.log(result);

.as-console-wrapper { max-height: 100% !important; top: 0; }

Thanks Hassan for your help. If I want "group by" parameters to be dynamic, ie i want to group by Phase/Step or Phase/Task based on certain conditions, how can i achieve that — RVS, Jan 22 '21 at 03:49
You can follow @NinaScholz approach by creating array of `groups` and then creating keys out of it. — Hassan Imam, Jan 22 '21 at 04:09
The syntax "??" in that approach is not working. Do I need to include any other library. It says Support for the experimental syntax 'logicalAssignment' isn't currently enabled — RVS, Jan 22 '21 at 04:19
See the edit above. It contains logic to group key based on dynamic keys. — Hassan Imam, Jan 22 '21 at 04:31

score 0 · Accepted Answer · answered Jan 19 '21 at 08:26

You could take the wanted keys for grouping in an array and build a compound key and take an object as hash table. For common keys add Task and Value values.

At the end take only the values from the object.

const
    data = [{ Phase: "Phase 1", Step: "Step 1", Task: "Task 1", Value: "5" }, { Phase: "Phase 1", Step: "Step 1", Task: "Task 2", Value: "10" }, { Phase: "Phase 1", Step: "Step 2", Task: "Task 1", Value: "15" }, { Phase: "Phase 1", Step: "Step 2", Task: "Task 2", Value: "20" }, { Phase: "Phase 2", Step: "Step 1", Task: "Task 1", Value: "25" }, { Phase: "Phase 2", Step: "Step 1", Task: "Task 2", Value: "30" }, { Phase: "Phase 2", Step: "Step 2", Task: "Task 1", Value: "35" }, { Phase: "Phase 2", Step: "Step 2", Task: "Task 2", Value: "40" }],
    groups = ['Phase', 'Step'],
    result = Object.values(data.reduce((r, o) => {
        const key = groups.map(k => o[k]).join('|');
        r[key] ??= { ...o, Task: '', Value: 0 };
        r[key].Task += (r[key].Task && ', ') + o.Task;
        r[key].Value += +o.Value
        return r;
    }, {}));

console.log(result);

.as-console-wrapper { max-height: 100% !important; top: 0; }

you could replace the line with an logical OR insta a nullish assingment operator: `r[key] = r[key] || { ...o, Task: '', Value: 0 };` — Nina Scholz, Jan 22 '21 at 08:02

Javascript group by 2 columns and sum up total

2 Answers2