Linq : GroupBy With Sum and Count,Min,Max function

Select Department, SUM(Salary) as TotalSalary
from Employee
Group by Department

var results = from r in Employees
                         group r by r.Department into gp
                         select new
                         {
                             DepartmentId = gp.Key,
                             TotalSalary = gp.Sum(a => a.Salary)
                         };

            foreach (var r in results)
            {
                Debug.Print(($@"""{r.DepartmentId}"" ""{r.TotalSalary}"""));
            }

C# Linq Group By on Multiple Columns

Let’s take this one more level, not only by Department, but we want to include the Country. For example, in France, how much is the total salary that I’m distributing for each department of Marketing, Research and Development, Accounting, and Finance employees. So we are grouping by multiple columns i .e by Department and Country columns. we are grouping total salary first by country and then by Department.

SQL Query:

Select Country, Department, SUM(Salary) as TotalSalary
from Employee
group by Country, Department

Linq Query:

var results = from r in Employees
                         group r by new { r.Department, r.Country } into gp
                         select new
                         {
                             DepartmentId = gp.Key.Department,
                             Country = gp.Key.Country,
                             TotalSalary = gp.Sum(a => a.Salary)
                         };

            foreach (var r in results)
            {
                Debug.Print(($@"""{r.DepartmentId}"" ""{r.Country}"" ""{r.TotalSalary}"""));
            }

Output:

Use Multiple aggregate functions with order by

let’s understand with an example, we are showing the totalsalary by country and Department and Let’s say I want the total number of employees as well.
Now, if we want to find out the total number of employees we can use the Count() function if you look at the Employee table, we’ve got 13 rows.And if I want to find the total number of employees within my Employee table, I can say Employee.count() and we get the total count, which is 13 and we will also use order by clause wit Group By.

Sql Query:

Select Country, Department, SUM(Salary) as TotalSalary,COUNT(Name) as TotalEmployees
from Employee
group by Country, Department
order by TotalSalary

Linq Query:

var results = (from r in employees
                           group r by new { r.Department, r.Country } into gp
                         select new
                         {
                             DepartmentId = gp.Key.Department,
                             Country = gp.Key.Country,
                             TotalSalary = gp.Sum(item => item.Salary),
                             TotalEmployees = gp.Count()
                         }).OrderBy(a=>a.TotalEmployees);

            foreach (var r in results)
            {
                Debug.Print(($@"""{r.Country}"" ""{r.DepartmentId}""  ""{r.TotalSalary}"" ""{r.TotalEmployees}"""));
            }

Output

How to apply the where clause with Group?

In the above scenario ,we want to filter the Group By result, let’s say I want only Research and Development department employee’s salaries, to filter the rows, we can use the where clause, where you can say Department is equal to 2.

Sql Query:

Select Country, Department, SUM(Salary) as TotalSalary,COUNT(Name) as TotalEmployees
from Employee
where Department=2
group by Country, Department
order by TotalSalary

Linq Query:

var results = (from r in employees
                           where r.Department==2
                           group r by new { r.Department, r.Country } into gp
                           select new
                           {
                               DepartmentId = gp.Key.Department,
                               Country = gp.Key.Country,
                               TotalSalary = gp.Sum(item => item.Salary),
                               TotalEmployees = gp.Count()
                           }).OrderBy(a => a.TotalEmployees);

            foreach (var r in results)
            {
                Debug.Print(($@"""{r.Country}"" ""{r.DepartmentId}""  ""{r.TotalSalary}"" ""{r.TotalEmployees}"""));
            }

Output:

Use Min and Max aggregate functions with Group By

Lets’ say, I want the min salary that I’m paying to employees by Department.

Sql Query:

Select Department, MIN(Salary) as TotalSalary
from Employee
Group by Department

Linq Query:

var results = (from r in employees
                           group r by  r.Department into gp
                           select new
                           {
                               DepartmentId = gp.Key,
                               MinDepartmentSalary = gp.Min(item => item.Salary),
                           });

Output

Lets’ say, I want the max salary that I’m paying to employees by Department.

Sql Query:

Select Department, Max(Salary) as MinDepartmentSalary
from Employee
Group by Department

Linq Query:

 var results = (from r in employees
                           group r by  r.Department into gp
                           select new
                           {
                               DepartmentId = gp.Key,
                               MaxDepartmentSalary = gp.Max(item => item.Salary),
                           });

            foreach (var r in results)
            {
                Debug.Print(($@"""{r.DepartmentId}""  ""{r.MaxDepartmentSalary}"""));
            }

Output

The post Split CSV with columns may contain ‘,’ Comma C# appeared first on Software Development | Programming Tutorials.