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 ?

2 Answers 2


For group by you can use the folowing query:

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

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);
  • 1
    Hi, thanks for reply, totally agree that there is no OOB way to use any aggregate function in CAML, I used your approach to solve the problem, with one difference, as I said I need to Calculate OrderCounts, so instead of CountOfOrders = ydate.Count() I used CountOfOrders = ydate.Sum(row => row.Field<int>("Order"))
    – Michael
    Aug 14, 2012 at 8:51

You can use group by clause:

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


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.