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

LINQ is actually a shortened form of Language Integrate Query. LINQ defines features that can be used to retrieve data from any type of data source. This is why LINQ is most important because data itself is the basic foundation of any program and using LINQ, data can be easily retrieve from different types of Data Sources.

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 extend our analysis to not only consider the department but also include the country. For instance, in France, we aim to calculate the total salary distributed across various departments such as Marketing, Research and Development, Accounting, and Finance. This requires grouping by multiple columns, specifically by Country and then by Department, to aggregate the total salary first by country and subsequently 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 illustrate this with an example. We're displaying the total salary by country and department, and suppose we also want to determine the total number of employees.

To find the total number of employees, we can utilize the `Count()` function. For instance, if we examine the Employee table and find 13 rows, we can retrieve the total count by using `Employee.Count()`, resulting in a count of 13. Additionally, we will utilize the `ORDER BY` clause in conjunction with the `GROUP BY` clause.

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 aim to filter the results obtained from the `GROUP BY` operation. Suppose we only want to consider the salaries of employees in the Research and Development department. To accomplish this filtering, we can utilize the `WHERE` clause, specifying that the 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