Solved

Linq sum

Posted on 2010-09-20
10
897 Views
Last Modified: 2013-11-11
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
0
Comment
Question by:cjJosephj
  • 5
  • 4
10 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 33715268
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
 

Author Comment

by:cjJosephj
ID: 33715589
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
 
LVL 53

Expert Comment

by:Dhaest
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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

Author Comment

by:cjJosephj
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

by:Dhaest
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,
                                    };

Open in new window

0
 
LVL 53

Expert Comment

by:Dhaest
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,
                                    };

Open in new window

0
 

Author Comment

by:cjJosephj
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

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

by:cjJosephj
ID: 33720519
Thanks Genius
0
 

Expert Comment

by:scm0sml
ID: 37694916
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Suggested Solutions

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question