Solved

# Linq sum

Posted on 2010-09-20
895 Views
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
0
Question by:cjJosephj
• 5
• 4

LVL 53

Expert Comment

ID: 33715268

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 Comment

ID: 33715589

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

LVL 53

Expert Comment

ID: 33715661
>> 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 Comment

ID: 33715671
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

LVL 53

Expert Comment

ID: 33715750
>> 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

LVL 53

Expert Comment

ID: 33715905
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 Comment

ID: 33716006

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

LVL 53

Accepted Solution

Dhaest earned 250 total points
ID: 33716238
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

Author Comment

ID: 33720519
Thanks Genius
0

Expert Comment

ID: 37694916
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is moâ€¦
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that undeâ€¦
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designedâ€¦
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage emâ€¦