I must be missing something fundamental about aggregate functions

I have a table that has three values

Table A Amount EmployeeID Status

I want to sum up

Select Sum(Amount) FROM Table A Where Status = 1 Group by EmployeeID

What is the equivalent in LLBLGen?

...and wouldn't it be great of someone who had more time built into the Manager (Core) templates the aggregate functions, with support for Filter, etc, and some pre-built expressions?

Something like

Core.Managers.InvoicesManager.Count( IExpression mExpression, IPredicate mFilter ) Core.Managers.InvoicesManager.Sum( IExpression mExpression, IPredicate mFilter ) Core.Managers.InvoicesManager.Max( IExpression mExpression, IPredicate mFilter )

etc.

Anyone have time to do this?

richardcherry wrote:

I must be missing something fundamental about aggregate functions I have a table that has three values

Table A Amount EmployeeID Status

I want to sum up

Select Sum(Amount) FROM Table A Where Status = 1 Group by EmployeeID

What is the equivalent in LLBLGen?

// Adapter:
IPredicate filter = PredicateFactory.CompareValue(TableAFieldIndex.Status, ComparisonOperator.Equal, 1);
GroupByCollection groupBy = new GroupByCollection();
groupBy.Add(EntityFieldFactory.Create(TableAFieldIndex.EmployeeID));
DataAccessAdapter adapter = new DataAccessAdapter();
float sum = (float)adapter.GetScalar(
    EntityFieldFactory.Create(TableAFieldIndex.Amount), null,
    AggregateFunction.Sum, filter, groupBy);
    
    
// selfservicing:
TableACollection col = new TableACollection();
IPredicate filter = PredicateFactory.CompareValue(TableAFieldIndex.Status, ComparisonOperator.Equal, 1);
GroupByCollection groupBy = new GroupByCollection();
groupBy.Add(EntityFieldFactory.Create(TableAFieldIndex.EmployeeID));
float sum = (float)col.GetScalar(TableAFieldIndex.Amount, null, AggregateFunction.Sum, filter, groupBy);

Otis wrote:

richardcherry wrote:

I must be missing something fundamental about aggregate functions I have a table that has three values

Table A Amount EmployeeID Status

I want to sum up

Select Sum(Amount) FROM Table A Where Status = 1 Group by EmployeeID

What is the equivalent in LLBLGen?

// Adapter:
IPredicate filter = PredicateFactory.CompareValue(TableAFieldIndex.Status, ComparisonOperator.Equal, 1);
GroupByCollection groupBy = new GroupByCollection();
groupBy.Add(EntityFieldFactory.Create(TableAFieldIndex.EmployeeID));
DataAccessAdapter adapter = new DataAccessAdapter();
float sum = (float)adapter.GetScalar(
    EntityFieldFactory.Create(TableAFieldIndex.Amount), null,
    AggregateFunction.Sum, filter, groupBy);
    
    
// selfservicing:
TableACollection col = new TableACollection();
IPredicate filter = PredicateFactory.CompareValue(TableAFieldIndex.Status, ComparisonOperator.Equal, 1);
GroupByCollection groupBy = new GroupByCollection();
groupBy.Add(EntityFieldFactory.Create(TableAFieldIndex.EmployeeID));
float sum = (float)col.GetScalar(TableAFieldIndex.Amount, null, AggregateFunction.Sum, filter, groupBy);

This returns a scalar value.

I want to return multiple SUM(AMOUNT), one for each EmployeeID.

Sorry I was not 100% clear.

AGAIN thank you so much for you help

richardcherry wrote:

Otis wrote:

richardcherry wrote:

I must be missing something fundamental about aggregate functions I have a table that has three values

Table A Amount EmployeeID Status

I want to sum up

Select Sum(Amount) FROM Table A Where Status = 1 Group by EmployeeID

What is the equivalent in LLBLGen?

some code...

This returns a scalar value.

I want to return multiple SUM(AMOUNT), one for each EmployeeID. Sorry I was not 100% clear. AGAIN thank you so much for you help

Ah, you then need a dynamic list:

adapter:

ResultsetFields fields = new ResultsetFields(1);
fields.DefineField(TableAFieldIndex.Amount, 0, "SumOfAmount");
fields[0].AggregateFunctionToApply = AggregateFunction.Sum;
RelationPredicateBucket filter = new RelationPredicateBucket();
filter.PredicateExpression.Add(PredicateFactory.CompareValue(TableAFieldIndex.Status, ComparisonOperator.Equal, 1));
GroupByCollection groupBy = new GroupByCollection();
groupBy.Add(EntityFieldFactory.Create(TableAFieldIndex.EmployeeID));
DataAccessAdapter adapter = new DataAccessAdapter();
DataTable results = new DataTable();
adapter.FetchTypedList(fields, results, filter, 0, null, true, groupBy);

selfservicing:

ResultsetFields fields = new ResultsetFields(1);
fields.DefineField(TableAFieldIndex.Amount, 0, "SumOfAmount");
fields[0].AggregateFunctionToApply = AggregateFunction.Sum;
PredicateExpression filter = new PredicateExpression();
filter.Add(PredicateFactory.CompareValue(TableAFieldIndex.Status, ComparisonOperator.Equal, 1));
GroupByCollection groupBy = new GroupByCollection();
groupBy.Add(EntityFieldFactory.Create(TableAFieldIndex.EmployeeID));
TypedListDAO dao = new TypedListDAO();
DataTable results = new DataTable();
dao.GetMultiAsDataTable(fields, results, 0, null, filter, null, true, groupBy, null, 0, 0);