I want to run the following query:

SELECT DISTINCT ContactName, ContactEmail, COUNT(*) AS Bookings
FROM         BookingList
GROUP BY ContactName, ContactEmail
HAVING    (NOT (ContactEmail = '')) AND (COUNT(*) > 20)
ORDER BY COUNT(*) DESC

I can get it all apart from the filtering and sorting using the aggregate field using this code:

Dim MyContactList As New DataTable
Dim fields As New ResultsetFields(3)
fields.DefineField(BookingListFields.ContactName, 0)
fields.DefineField(BookingListFields.ContactEmail, 1)
fields.DefineField(BookingListFields.ContactEmail, 2, SD.LLBLGen.Pro.ORMSupportClasses.AggregateFunction.Count)
Dim filter As IPredicateExpression = New PredicateExpression
filter.Add(BookingListFields.ContactEmail <> "")
'filter.AddWithAnd
Dim sorter As ISortExpression = New SortExpression
'sorter.Add()
Dim GroupBy As IGroupByCollection = New GroupByCollection
GroupBy.Add(fields(0))
GroupBy.Add(fields(1))
Dim MyDynamicList As New TravBase.DaoClasses.TypedListDAO()
MyDynamicList.GetMultiAsDataTable(fields, MyContactList, 0, sorter, filter, Nothing, False, GroupBy, Nothing, 0, 0)

What do I need to add to my sorter and filter to sort and filter on the aggregate field COUNT(*) (Or in this case COUNT(ContactEmail))

I've searched the forums and the help without any joy so far...

Hi JMitchell, You can achieve that in this way: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=9786&StartAtMessage=0&#54480

Cheers,

I thought I'd asked this before! Sorry.

That answers the sorting. sorter.Add(New SortClause(fields(2), Nothing, SortOperator.Descending))

How can I do the filter? I've tried this: filter.AddWithAnd(New FieldCompareValuePredicate(fields(2), ComparisonOperator.GreaterEqual, 20))

but it doesn't work. It filters on the field and ignores the fact that fields(2) has a count applied to it.

This didn't work either although the help seems to suggest this is what I should be doing... filter.AddWithAnd(BookingListFields.ContactEmail.SetAggregateFunction(SD.LLBLGen.Pro.ORMSupportClasses.AggregateFunction.Count) >= 20)

This should work, although I really don't know much vb so, it may contain some syntax errors:

Dim MyContactList As New DataTable

        Dim fields As New ResultsetFields(3)
        
            fields.DefineField(BookingListFields.ContactName, 0,"ContactName")
            fields.DefineField(BookingListFields.ContactEmail, 1,"ContactEmail")
            fields.DefineField(BookingListFields.ContactEmail, 2, "Count")
            
            fields["Count"].SetAggregateFunction(AggregateFunction.Count)
        
        Dim havingFilter As IPredicateExpression = New PredicateExpression
            
            havingFilter .Add(new FieldCompareValuePredicate(fields["ContactEmail"], null, ComparisonOperator.Equal, "",true))
            havingFilter .AddWithAnd(new FieldCompareValuePredicate(fields["Count"], null, ComparisonOperator.GreaterThan, 20))

        Dim sorter As ISortExpression = New SortExpression

            sorter.Add(New SortClause(fields(2), Nothing, SortOperator.Descending))

        Dim GroupBy As IGroupByCollection = New GroupByCollection
            
            groupBy.Add(fields(0))
            groupBy.Add(fields(1))          
            groupBy.HavingClause = havingFilter

        Dim MyDynamicList As New TravBase.DaoClasses.TypedListDAO()
        MyDynamicList.GetMultiAsDataTable(fields, MyContactList, 0, sorter, Nothing, Nothing, False, groupBy, Nothing, 0, 0)

That was it - putting the filter into the groupBy.HavingClause. I didn't need to change anything else.

Cheers goose.