Is there a way to achieve a resultset using LLBLGen (preferably using a typed list) that gets the same data as the following SQL query?

insert into #tmpVendor
   select 
      VendorID, sum(Amount) as TotalAmount
   from 
      Invoice
   where
      Amount > AmountPaid
   group by 
      VendorID

select
   v.Name, v.Address, v.Telephone, t.TotalAmount
from
   Vendor v
left outer join #tmpVendor t on
   t.VendorID = v.ID

Since, there can be a lot of data involved, i want to use paging as well.

I found a way to get the desired result using derived tables. First I created a view

create view VendorVw as
    select
        v.Name, v. Address, v.Telephone, t.TotalAmount
    from 
        Vendor v
    left outer join (
        select VendorID, sum(Amount) as TotalAmount 
            from where Amount > AmountPaind
            group by VendorID ) as t
        on t.VendorID = v.ID

Next, I added the TypedView to the project.

Now I am able to use FetchTypedView to get the results and use the paging facilities as well. The disadvantage of this approach is that each time I want to use a construction like this I need to add a view to the database. Correct me if I'm wrong, but I don't see a method to create a query using derived tables using LLBLGen C# code.

Derived tables aren't supported at the moment.

Your query is indeed a tricky one. I don't see a way to do the query with the temptable and without derived tables in the FROM clause using LLBLGen Pro constructs.

There might be a way, by using a subquery in which you use the groupby. If you could rewrite teh query (I'm not sure if it's possible to do it that way, but it might) and manage to get the same results, you could write it using llblgen pro constructs, by using a FieldCompareSetPredicate and specifying the groupby clause there.