How to use GroupBy in CAML query

Question

I have simple list, consisting of 2 columns - Date, Order. Now what I want to do, is to GroupBy Date and count orders. So, for example if I have following items

Date        Order

08/10/2012  5 
08/13/2012  1 
08/13/2012  2

I need to get following results

Date        OrderCount

08/10/2012  5
08/13/2012  3

As far as I know, I can use CAML query and use GroupBy, but I can't seem to find how to use aggregate function Count ( like in sql ). I know I can read all items from list, and then use linq to objects, but it's the last option I'd like to consider. Any ideas how can I do this ?

Answer 1

For group by you can use the folowing query:

<Query>
 <GroupBy Collapse="TRUE">
   <FieldRef Name="Title" />
 </GroupBy>
</Query>

But for counting of the items in the result set there is no direct OOB way. You can one thing when you get result set from the above query then from the item collection get it's count property and use it.

Otherwise, I can think other option:

get listitem in datatable and use linq to count on fields

 var query = from row in yourdatatable.AsEnumerable()
        group row by row.Field<Date>("Date") into ydate
        orderby ydate.Key
        select new
        {
            Name = ydate.Key,
            CountOforders = ydate.Count()
        };

// print result
foreach (var yourdate in query)
{
    Console.WriteLine("{0}\t{1}", yourdate.Name, yourdate.CountOforders);
}

Answer 2

You can use group by clause:

<GroupBy
  Collapse = "TRUE" | "FALSE">
  <FieldRef Name = "Field_Name"/>
</GroupBy>

MSDN Link