Summarize by column with totals of other

[MaaPer]

Describe the issue/error/question

I have this information.

I wanted to summarize and get as results the total USD by colors, so the result should be one table with three rows (one per color) and the amount equal to the subtotal for each color.

Any idea if was possible to achieve with no-code or low-code?

Please share the workflow

Information on your n8n setup

• n8n: running on synology Docker

[MutedJam]

Hey @MaaPer, I am afraid this isn’t possible completely without code. However, the code required isn’t too bad. You could use the below code in a Function node to retrieve the sum for each colour:

const colours = [...new Set(items.map(e => e.json.producto))];

let results = [];

for (colour of colours) {
  results.push({
    json: {
      producto: colour,
      sum: items
        .filter(e => e.json.producto === colour)
        .map(e => e.json.USD)
        .reduce((a, b) => a + b, 0)
    }
  })  
}

return results;

The above example first extracts all unique colours, then adds a new result object for each colour, by first filtering all elements where productco equals the current colour and keeping only their USD values, then adding them all together.

This is how it looks in a workflow:

Hope this helps! Let me know if you have any questions on this example

[dickhoning]

@MutedJam awesome! And now part of my data-transformation collection

[MaaPer]

It’s really awesome! Many thanks @MutedJam!!!

Definitively I need to learn some coding with JavaScript, I will search the book of “JS for Dummies”

Until now I was putting the information on google sheets, then summarize using a query formula on another sheet of the same spreadsheet and then calling again the summarize info from google, what is a non-professional solution compared to the right one that you shared with us.

I will try to adapt the code to my real scenario (a little more complex) and will contact to you if I get blocked on the way.

Thanks!!!

[Jon]

@dickhoning is this collection available somewhere?

[MutedJam]

You are most welcome, glad to hear this helps! Give me a shout anytime if any of the code used above is unclear.

My go-to book for data transformations is the “JavaScript Pocket Reference” from David Flanagan btw (well, the German translation “JavaScript – kurz & gut” of it to be very precise). I can highly recommend it in case you’re still looking for a book to get. It’s not a complete guide on learning JavaScript but it has not let me down so far when I needed to transform data and didn’t know how.

What’s also worth keeping in mind when working with the Function node is the n8n data structure.

[MaaPer]

@MutedJam

My go-to book for data transformations is the “JavaScript Pocket Reference” from David Flanagan btw (well, the German translation “JavaScript – kurz & gut” of it to be very precise). I can highly recommend it in case you’re still looking for a book to get. It’s not a complete guide on learning JavaScript but it has not let me down so far when I needed to transform data and didn’t know how.

I will take note of the review, however I’m afraid I will need to find something very low level for beginners to get enough level to be able to understand different script codes. (Think that will be my first approach to a new code language from the times of Turbo Pascal )

I suppose that general JS will be the same to use in n8n and that there is no need of any special JS for n8n. Right?

[MutedJam]

That’s right, n8n only adds a few additional objects and methods providing data from your workflow, these are documented here: Expressions | Docs (n8n.io).

[dickhoning]

@Jon not yet. It’s still pretty much work in process. My plan is to wait for the notes functionality in n8n, and then properly document all the examples I worked out.

Jus to give you some idea of the current status

[MaaPer]

I will follow you!!!

[Jon]

That is the most beautiful thing I have ever seen.