How do i group by fields that appear in the where clause but not in the select statement?

You will want to use a GroupByClause when filling your collection. The groupbyclause can be used like this. This example uses a typed list.

SumTestTypedList sum = new SumTestTypedList(); IEntityFields fields = sum.BuildResultset(); GroupByCollection groupByClause = new GroupByCollection(); groupByClause.Add(fields["ProjectId"]); sum.Fill(0, null, false, null, null, groupByClause);

You should just fill the groupByClause with the fields that are used in your filter.

Your example presumes that i am grouping by a field already in the select statement. This is not the case. I am pulling fields from joined tables to to use in the where (filter) part of the query. SQL Server 2000 complains that the fields used in the where clause must also be part of the group by collection. So i need to add a field to the group by collection that is not in the field collection, how do i do this?

John wrote:

Your example presumes that i am grouping by a field already in the select statement. This is not the case. I am pulling fields from joined tables to to use in the where (filter) part of the query. SQL Server 2000 complains that the fields used in the where clause must also be part of the group by collection. So i need to add a field to the group by collection that is not in the field collection, how do i do this?

Could you give an example of a query which fails?

because fields used in a where clause don't have to be in the groupby clause. The fields in the select list either have to be in the groupby clause OR have to have an aggregate function applied to them, IF a groupby clause is specified.

This query works fine for example:

select sum(unitprice)
from [order details] od inner join orders o
    on od.orderid = o.orderid
where o.customerid = 'CHOPS'

My mistake . The error was because I was using feilds in the 'Order By' clause and not including them in the select and group by clauses. Once I added the Order by fields to the select and then the group by, everything worked as expected