LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: manson88

Hi,
I am new to BRE, and I am trying to replicate the MsAccess Group By Function in BRE.

Here's what I am trying to get.

My source data have duplicate records. Please see example below.

ColumnA ColumnB
1 a
1 a
1 b
1 b
1 c
2 a
2 a

In MsAccess, if I use GroupBy function, using the SQL statment :SELECT ColumnA, ColumnB FROM [source data]
GROUP BY ColumnA, ColumnB; then I will get the result below

ColumnA ColumnB
1 a
1 b
1 c
2 a

How can this be done in BRE?

Thx

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

The simple version is to simply use the SORT node with these parameters:

CompareOrderExpr = ColumnA, ColumnB
Unique=True


=======

But, if you need to say count the records... then you need to use the AGG node.

You need first need to SORT the input.. add a SORT node, and give it these parameters.

CompareOrderExpr = ColumnA, ColumnB
Unique=False

Connect that output to an AGG node.
Then, for the AGG:

Script= c=count() emit *,c where lastInGroup

GroupBy= ColumnA, ColumnB

Originally posted by: manson88
					

Hi,

I am new to BRE, and I am trying to replicate the MsAccess Group By Function in BRE.
			

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: rpigneri

Just a quick note: if you are using 4.1.6 or later, you can use the Agg Ex node in place of the Agg node. The Agg Ex node provides the sort functionality right out of the box so that you can get your work done with less fuss!

You can find out more about the Agg Ex node in the node help.

Hope that helps,

Rocco