Is it posible to use DefineField and AggregateFunction.SumDistinct with two fields in LLBL GenPro 3.1? For example I have this:

ResultsetFields myFields = new ResultsetFields(2);

//I want this amount to be distinct but with specific userId
myFields.DefineField(UserFields.Amount, 0, AggregateFunction.SumDistinct); 
myFields.DefineField(UserFields.Id, 1, "ResultCount", AggregateFunction.CountDistinct);

In SQL, I want it end up with something like:

SELECT DISTINCT SUM(u.Amount), u.Id 
FROM Users u
  INNER JOIN someOtherTablesat ON sot.UserId = u.Id
GROUP BY 
  u.Id

This join I have in LLBL and it duplicates amount, because someOtherTable has few data for same user. I need that join for some another reason. So, is it possible to do that with LLBL and how?

If you are grouping by the u.ID as in the shown SQL, then why are you using an aggregate on the same field?

Walaa wrote:

If you are grouping by the u.ID as in the shown SQL, then why are you using an aggregate on the same field?

I'm sorry, that is not good example in SQL what i wrote. I have one table witch primary key is in another table foreign key. In first table I have amount, but that amount duplicates if I had more than one data related with second table. I need second table because I have some data witch are in WHERE clause. Basically, I need sum of amount from first table, but second table is needed due to condition.

You need to rewrite the SQL or re-think the way you want the results. For instance, you could put the condition in a WHERE instead of a JOIN:

The following two SQL would be if you use the FieldCompareSetPredicate:

SELECT DISTINCT SUM(u.Amount), u.Id 
FROM Users u
WHERE u.Id IN (SELECT DISTINCT sot.UserId FROM someOtherTablesat)
GROUP BY 
u.Id

Or the EXISTS variant:

SELECT DISTINCT SUM(u.Amount), u.Id 
FROM Users u
WHERE EXISTS (SELECT sot.UserId FROM someOtherTablesat sot WHERE u.Id = sot.UserId)
GROUP BY 
u.Id

Or, if a JOIN is necessary, try to user a DerivedTable and DynamicRelation:

SELECT DISTINCT SUM(u.Amount), u.Id 
FROM Users u
JOIN (SELECT sot.UserId FROM someOtherTablesat) sot ON u.Id = sot.UserId
GROUP BY 
u.Id

Real example in SQL is like this:

SELECT
    DISTINCT SUM(a.IznosUsluga) as IznosUsluga,
    SUM(a.Participacija) as Participacija,
    SUM(a.IznosSanMedMat) as IznosSanMedMat
FROM(
    SELECT 
        fo.IznosUsluga,
        fo.Participacija,
        fo.IznosSanMedMat,
        fo.Id
    FROM inv.FakturaOsiguranik fo
        INNER JOIN inv.FakturaUsluga fu ON fu.FakturaOsiguranikId = fo.Id
    WHERE
        fo.DatDo >= '2017-11-01'
        AND fu.DepartmentAdmissionTranId IN (787, 789, 790, 788)
    GROUP BY 
        fo.IznosUsluga,
        fo.Participacija,
        fo.IznosSanMedMat,
        fo.Id
 )a

When I run this query

SELECT 
        fo.IznosUsluga,
        fo.Participacija,
        fo.IznosSanMedMat,
        fo.Id
    FROM inv.FakturaOsiguranik fo
        INNER JOIN inv.FakturaUsluga fu ON fu.FakturaOsiguranikId = fo.Id
    WHERE
        fo.DatDo >= '2017-11-01'
        AND fu.DepartmentAdmissionTranId IN (787, 789, 790, 788)

result is

IznosUsluga Participacija   IznosSanMedMat  Id
764.44                  50.00   39107.040000    1413364
764.44                  50.00   39107.040000    1413364
3429.34                 50.00   5838.450000 1413365
3429.34                 50.00   5838.450000 1413365

So I think this is the only way. I don't know how to do this with LLBL, so I will run it like procedure.

Simply you need to use something like Exists or IN as David has noted. Example from Northwind:

Select SUM(Freight) from Orders
Where OrderID IN 
(
    Select OrderID from [Order Details] where ProductID = 1
)

var fields = new ResultsetFields(1);
fields.DefineField(OrderFields.Freight, 0, AggregateFunction.Sum);

var bucket = new RelationPredicateBucket();
bucket.PredicateExpression.Add(new FieldCompareSetPredicate(
    OrderFields.OrderId, null, OrderDetailFields.OrderId, null, SetOperator.In, (OrderDetailFields.ProductId ==1)));

DataTable dynamicList = new DataTable();

using (var adapter = new DataAccessAdapter())
{
    adapter.FetchTypedList(fields, dynamicList, bucket);
}

You can define as many aggregate fields as you want. You don't need to use GroupBy unless you have a field in the select list that's not aggregated, then you need to GroupBy this field.

Walaa wrote:

Simply you need to use something like Exists or IN as David has noted. Example from Northwind:

Select SUM(Freight) from Orders
Where OrderID IN 
(
    Select OrderID from [Order Details] where ProductID = 1
)

var fields = new ResultsetFields(1);
fields.DefineField(OrderFields.Freight, 0, AggregateFunction.Sum);

var bucket = new RelationPredicateBucket();
bucket.PredicateExpression.Add(new FieldCompareSetPredicate(
    OrderFields.OrderId, null, OrderDetailFields.OrderId, null, SetOperator.In, (OrderDetailFields.ProductId ==1)));

DataTable dynamicList = new DataTable();

using (var adapter = new DataAccessAdapter())
{
    adapter.FetchTypedList(fields, dynamicList, bucket);
}

You can define as many aggregate fields as you want. You don't need to use GroupBy unless you have a field in the select list that's not aggregated, then you need to GroupBy this field.

That's it . Thanks a lot.