Solved

Linq sum

Posted on 2010-09-20
10
893 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

760 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now