# Total sum of a column by Group using column expressions (without join back or using group loop)

Hi friends

I would like to calculate the total of a column by category without using the joiner node. My curiosity is how to execute this using column expressions to mimic the WINDOWs FUNCTIONS like SQL.

Yes, I know it should be possible to do this using Python or Java Snippet.

I’m studying SQL (Postgres), and since I use column expressions a lot, I was curious to know if it’s possible to do it through them or any node that doesn’t depend on the group loop.

In SQL (postgres)
it is possible to use do the function OVER () , with PARTITION ()
And achieve powerful results.

In this example I’m using “avg” instead “sum”. Is the sum all all values.

I could, group by any categories’ em bring the average, and bring the avg of the a column with a filter inside. Example: the sum of column where the year is greater than 2000.

Well, in Knime, I could do some “group loop”, and the result of the sum, store into a variable and print in the table, using joiner etc…

It’s possible to mimic, like SQL in one “simple” code?

I imagine that I will have to use group loop, or variable, joiner, but this post is for curiosity, learning.

Let’s go to a simple example for Knime:

Simple example

Hi @Felipereis50 , I’m possibly not fully understanding the question, but it’s not a task I would expect Column Expressions to be particularly suitable for), but I wouldn’t use a loop for it either.

I would think the most straightforward way of providing similar output to the sql functions you describe would be with the GroupBy node rather than a group loop.

In the simple case, grouping by the “Category” column, and deriving sum(Value) and then joining the result back to category would achieve the first output, whilst in the second case, initially filtering rows on Year, then doing the equivalent GroupBy and Join as before would achieve the second output.

1 Like

Hi @takbb

I imagined that would really be the only solution using nodes.
But one never knows the power of column expressions.

Perhaps the closest thing to do, with formulas similar to SQL, would be using Python or Java.

I appreciate the help and example provided.

1 Like

You’re welcome as always @Felipereis50 .

Mind you, if you really have the urge just to write SQL against your KNIME tables, … you could try out my KNIME Table Session Connector component, and its sister-component the KNIME Table Connector

and

Just attach to the table KNIME table and write your SQL (H2 syntax)… using your choice of the DB nodes