asked on

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

Dirk Haest

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

cjJosephj

ASKER

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 ?

Dirk Haest

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

cjJosephj

ASKER

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

Dirk Haest

>> 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

Dirk Haest

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

cjJosephj

ASKER

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

ASKER CERTIFIED SOLUTION

Dirk Haest

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cjJosephj

ASKER

Thanks Genius

scm0sml

I got the answer here:
http://stackoverflow.com/questions/9615558/linq-sum-is-rounding