?
Solved

Linq sum

Posted on 2010-09-20
10
Medium Priority
?
899 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Featured Post

How to Create Failover DNS Record Sets in Route 53

Route 53 has the ability to easily configure DNS record sets specifically for failover scenarios. These failover record sets can be configured to failover to full-blown deployments in other regions or to a static HTML page that informs your customers of the issue.

Question has a verified solution.

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

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
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…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses

801 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