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");                           
	   };

Open in new window


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

Open in new window

     Thanks
cjJosephjAsked:
Who is Participating?
 
DhaestCommented:
           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
 
DhaestCommented:
Can you give me some more information please. What exactly do you want to retrieve ?

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");
            dt.Columns.Add(new DataColumn("department"));
            dt.Columns.Add(new DataColumn("employeeId"));
            dt.Columns.Add(new DataColumn("score",System.Type.GetType("System.Decimal"))); 

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

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

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

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

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

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

            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"))
                                    };

Open in new window

0
 
cjJosephjAuthor Commented:
Thanks for your reply,

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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
DhaestCommented:
>> 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
 
cjJosephjAuthor 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
 
DhaestCommented:
>> 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,
                                    };

Open in new window

0
 
DhaestCommented:
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,
                                    };

Open in new window

0
 
cjJosephjAuthor 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
 
cjJosephjAuthor Commented:
Thanks Genius
0
 
scm0smlCommented:
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.

All Courses

From novice to tech pro — start learning today.