Group by and count rows

Hello,

How do I group by and count rows?

image

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

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:

image

You just need to rename the last column.

8 Likes

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.

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