Linq Group by and SUM of column?

Question

0.00/5 (No votes)

See more:

I have a datatable of 3 columns.

C#

Category | Product | Counts
--------------------------------
1        |  Tea    | 100
1        |  Cofee  | 102
2        |  Pen    | 23
2        |  Den    | 200
2        |  Wen    | 60
2        |  Gen    | 12
3        |  Tip    | 12
3        |  Gip    | 12
3        |  Dip    | 12

i want to group it by category with LINQ and show the sum of the Counts(column) under Category

e.x.

C#

Category |  Counts
--------------------------------
1        |  202
2        |  295
3        |  36

C#

DataTable MainData = obj.DATA();

        var query = from b in MainData.AsEnumerable()

                   group b by  b.Field<string>("Category")  into Grp
                   let _count =  Convert.ToInt32( Grp.FirstOrDefault().Field<string>("_count")) 
                                 
                  orderby __count descending
                  select new
                         {
                             Category= Grp.Key,
                          Count = _count
                         };

its just counting the rows.

Posted 29-May-14 0:44am

shaprpuff

Updated 29-May-14 0:47am

Thanks7872

v2

Add a Solution

3 solutions

Solution 1

This link http://social.msdn.microsoft.com/Forums/en-US/3b13432a-861e-45f0-8c25-4d54622fbfb4/linq-group-and-sum-table?forum=linqprojectgeneral[^] should give you the solution to your problem.

Part of your issue is that you aren't actually doing a sum in your linq.

Posted 29-May-14 0:52am

Pheonyx

Solution 3

I think using .Sum() function instead of .FirstOrDefault() will help you

Posted 29-May-14 1:06am

Madhav Hatwalne

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

DamithSL · Accepted Answer · 2014-05-29T00:54:00

Solution 2

C#

var result = MainData.AsEnumerable()
.Select(r=> new{ Category = r.Field<string>("Category"), 
                 Count =Convert.ToInt32( r.Field<string>("_count"))})
.GroupBy(x=>x.Category).Select(g=>new{Category=g.Key, Count=g.Sum(c=>c.Count)});

Posted 29-May-14 0:54am

DamithSL

Updated 29-May-14 1:44am

v5

Linq Group by and SUM of column?

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Linq Group by and SUM of column?

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us