Solved

Linq sum

Posted on 2010-09-20
10
896 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

856 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