Linq sum

Hi,

I need to get the sum of employees scores for each department using linq.
I only need scores from departments that have more than 20 employees but
not sure Where to put the Where clause also not sure if the query below is correct

``````	 var departmentquery = from row in dt.AsEnumerable()
group row by row["department"] into grp
select new   {
Sum = grp.Sum(r => r.Field<decimal>("score");
};
``````

I have written the sql that brings back the correct data,is it possible for this query to be converted to linq and used in the above code.
``````select e.departmentId,round(cast(sum(score)as decimal(10,2))/(SELECT sum(score) FROM table),2)
From table t
--joins etc
GROUP BY employeeId
``````
Thanks
Who is Participating?

Commented:
Int16 sumTotal = (Int16)dt.AsEnumerable().Sum(x => x.Field<Int16>("score"));

var departmentquery = from row in dt.AsEnumerable()
group row by row["department"] into grp
where grp.Count() > 1
select new
{
deparmtent = grp.Key,
Sum = grp.Sum(r => r.Field<Int16>("score")),
Percentage = 100 * (decimal)grp.Sum(r => r.Field<Int16>("score")) / (decimal)sumTotal,
Average = grp.Average(r => r.Field<Int16>("score"))
};
0

Commented:

this query will  give you all the departments, and for each department the sum. There need to be more than (in this example) 1 employee

var departmentquery = from row in dt.AsEnumerable()
group row by row["department"] into grp
where grp.Count() > 1
select new   {
deparmtent = grp.Key,
Sum = grp.Sum(r => r.Field<decimal>("score"))
};
``````            DataTable dt = new DataTable("department");

DataRow datarow = dt.NewRow();
datarow[0] = "1";
datarow[1] = "1";
datarow[2] = 10;

datarow = dt.NewRow();
datarow[0] = "1";
datarow[1] = "2";
datarow[2] = 10;

datarow = dt.NewRow();
datarow[0] = "1";
datarow[1] = "3";
datarow[2] = 10;

datarow = dt.NewRow();
datarow[0] = "2";
datarow[1] = "1";
datarow[2] = 10;

datarow = dt.NewRow();
datarow[0] = "2";
datarow[1] = "2";
datarow[2] = 10;

datarow = dt.NewRow();
datarow[0] = "3";
datarow[1] = "1";
datarow[2] = 10;

var departmentquery = from row in dt.AsEnumerable()
group row by row["department"] into grp
where grp.Count() > 1
select new   {
deparmtent = grp.Key,
Sum = grp.Sum(r => r.Field<decimal>("score"))
};
``````
0

Author Commented:

For some reason when I use

Sum = grp.Sum(r => r.Field<decimal>("score"))

I get Specified cast is not valid.

Is because the datatype in my database is int and I’m using <decimal>?
Also is there a reason why we need to use decimal ?
0

Commented:
>> Is because the datatype in my database is int and I’m using <decimal>?

Try it to do it with integer than

Sum = grp.Sum(r => r.Field<integer>("score"))
0

Author Commented:
I did try interger but the scores do not seem correct

I need my counts to be a percentage of the total score

This what I currently do in my stored procedure

set @sumdata = (select sum(data) as data from table)
BEGIN

select  department_Id, (sum(data) / @sumdata )*100  as data from table group by department_Id

Thanks
0

Commented:
>> I need my counts to be a percentage of the total score

The query I provided just gives the total for that department...

I'm not sure if you can retrieve it in one query.... I'll take a look

``````            Int16 sumTotal = (Int16)dt.AsEnumerable().Sum(x => x.Field<Int16>("score"));

var departmentquery = from row in dt.AsEnumerable()
group row by row["department"] into grp
where grp.Count() > 1
select new   {
deparmtent = grp.Key,
Sum = grp.Sum(r => r.Field<Int16>("score")),
sumTotal,
};
``````
0

Commented:
Is this what you are looking for
``````Int16 sumTotal = (Int16)dt.AsEnumerable().Sum(x => x.Field<Int16>("score"));

var departmentquery = from row in dt.AsEnumerable()
group row by row["department"] into grp
where grp.Count() > 1
select new
{
deparmtent = grp.Key,
Sum = grp.Sum(r => r.Field<Int16>("score")),
Percentage = 100 * (decimal)grp.Sum(r => r.Field<Int16>("score")) / (decimal)sumTotal,
};
``````
0

Author Commented:

I decided to get the average score instead of the sum so if a the scores goes up to 20 I will just display the average score for that department e.g  9
0

Author Commented:
Thanks Genius
0

Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.