Group by and count rows

[Tiste_sg]

Hello,

How do I group by and count rows?

For instance, here I’m trying to get:

Col 1, Col 2, Count
a, 1, 2
a, 2, 1
b, 1, 1
b, 2, 1
b, 3, 1
c, 1, 3

I tried with a combination of nodes including the GroupBy and ColumnAggregator but could not get it to work.

Many thanks
B

[bobpeers]

There’s a few ways you could do this but your issue is that if you are using both column as groupings you don’t have any columns left to do an aggregation.

The simplest way is just introduce another column, here I use the Constant Value Column, to add an extra column. The contents are not important but now you can use this extra column to do a count.

The Group By looks like this:

And the output:

You just need to rename the last column.

[rolandnemeth]

Further solution:

1. Get the row IDs with the help of ROWID node
2. Count the unique row IDs by grouping columns with the GroupBy node.