Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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 ?

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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);
}
share|improve this answer
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 moore Aug 14 '12 at 8:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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