# GroupBy counting occurences

I’m trying to use a GroupBy node to count the occurrence of a value in a column. Eg, table like this (from a cross joiner):

col1 col2 value
A A 0
A B 1
A C 2
B A 2
B B 0
B C 0
and so on in a big table,

I want to group by the first column and count how many of a certain value there are, eg for 0 output
A 1
B 2

I think the GroupBy node is what I need, but I’m not sure?

To answer my own question, I found a way but it doesn’t seem elegant.
I can filter by the value I want to count, then GroupBy the main column using the count function.
At the moment I don’t need the other rows, but if I need them later I can always bring them back with a concatenate.

Hello @phick

For your convenience… just replicate the target column and group over it.

Rule Engine: `\$column1\$ LIKE \$column1\$ => \$column1\$`

BR

I’m not sure I understand how this helps?

To give more detail, I am generating a large cross table from two data sets (call them A and B), then calculating a score based on various column values (this is currently an integer between 0-5). What I am working it out is how many times an entry in the input table scores 0, so the output would be the same number of rows as table A, with a column value varying from 0 up to the number of rows in column B.

Hi phick,
the result you are describing can be achieved by using ‘col1’ and ‘value’ as the columns to group!
Best
E

1 Like

You only need to develop a little more… I am just delivering your desired output

group by the first column and count how many of a certain value there are, eg for 0 output
A 1
B 2
At the moment I don’t need the other rows, but if I need them later…

From my point of view the key is the Rule Engine node. the rest is displaying arrangement

BR

Just to confirm that the concept is clear

That’s perfect, exactly what I was trying to do. I group by my first table (ie col1) and the score value, then count how many of the second table (col2) get each score.

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.