0

I want to run the below SQL query in linq using a datatable

select 
      SUM(cast(Percentage as int))
     ,Subject 
from tablename 
where Student_ID = xxx 
group by Subject_ID

How can I do it? Any help would be appreciated

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
dazzling kumar
  • 584
  • 1
  • 8
  • 17
  • https://code.msdn.microsoft.com/LINQ-Aggregate-Operators-c51b3869#SumGrouped – BlackICE Apr 20 '15 at 12:13
  • Please refer to [here][1] I hope this helps. [1]: http://stackoverflow.com/questions/15512628/sql-query-with-distinct-and-sum – Thealon Apr 20 '15 at 12:32
  • i have tried this ds1.Tables[i].AsEnumerable().GroupBy(row => row.Field("Percentage")).Select(group => group.First()).CopyToDataTable(); – dazzling kumar Apr 20 '15 at 12:46

1 Answers1

1

Use Enumerable.GroupBy, Sum and an anonymous type to store the result:

var query =  tablename.AsEnumerable()
    .Where(row => row.Field<int>("Student_ID") == xxx)
    .GroupBy(row => new { SubjectID = row.Field<int>("Subject_ID"), Subject = row.Field<string>("Subject") })
    .Select(g => new 
    { 
        SumPercentage = g.Sum(r => r.Field<int>("Percentage")),
        Subject = g.Key.Subject, SubjectID = g.Key.SubjectID
    });

I don't know why you need to cast Percentage to int, but i'm pretty sure that you get it.

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939