Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum

In this article, we will learn about using Linq to SQL Group by and Sum in Select statements using C#. We have already covered how to use the group by clause in SQL queries. Now, in this post, we will demonstrate performing the Group by and Sum operations within a Select statement using LINQ queries.

I'm assuming that you are familiar with SQL queries and the group by clause. If not, please refer to the following articles:"

• How to use group by in SQL Server query?
• Understand Group by With Linq C#.

The GROUP BY clause is used in with the SELECT statement to arrange matching data into groups, the GROUP BY clause pursues the WHERE clause in a SELECT statement, and then the ORDER BY clause.

Syntax
The basic syntax of a GROUP BY clause in LINQ to SQL is given below as shown in the following code block.

var results = from r in table group r by r.column1 into gp select new {
  column = gp.Key, sum = gp.Sum(item => item.column2)
};

I have a list of Employee,Now I want to group the list based on the Employee Country and return an object containing the Country and the TotalSalary for each Country.

Employee Collection

public class Employee {
public List < Employee > Getemployees() {
    List < Employee > employess = new List < Employee > ();
    employess.Add(new Employee {
      Name = "Du monde entier", Country = "IN", Address = "67, rue des Cinquante Otages", Salary = 5000.00, Department = "HR"
    });
    employess.Add(new Employee {
      Name = "Chop-suey Chinese ", Country = "USA", Address = "Hauptstr. 29", Salary = 2500.00, Department = "IT"
    });
    employess.Add(new Employee {
      Name = "France restauration", Country = "UK", Address = "54, rue Royale", Salary = 2000.00, Department = "FD"
    });
    employess.Add(new Employee {
      Name = "Howard Snyder", Country = "FR", Address = "2732 Baker Blvd.", Salary = 2400.00, Department = "FD"
    });
    employess.Add(new Employee {
      Name = "Carlos Hernández", Country = "UK", Address = "Carrera 22 con Ave. Carlos Soublette #8-35", Salary = 3500.00, Department = "SD"
    });
    employess.Add(new Employee {
      Name = "France restauration", Country = "BR", Address = "54, rue Royale", Salary = 3200.00, Department = "FD"
    });
    employess.Add(new Employee {
      Name = "John Steel", Country = "USA", Address = "2732 Baker Blvd.", Salary = 2800.00, Department = "HR"
    });
    employess.Add(new Employee {
      Name = "Renate Messner", Country = "FR", Address = "Carrera 22 con Ave. Carlos Soublette #8-35", Salary = 2700.00, Department = "FD"
    });
    employess.Add(new Employee {
      Name = "Horst Kloss", Country = "IN", Address = "Rua da Panificadora, 12", Salary = 2400.00, Department = "SD"
    });
    employess.Add(new Employee {
      Name = "Guillermo Fernández", Country = "USA", Address = "Carrera 22 con Ave. Carlos Soublette #8-35", Salary = 3500.00, Department = "SD"
    });
    employess.Add(new Employee {
      Name = "Georg Pipps", Country = "FR", Address = "54, rue Royale", Salary = 3200.00, Department = "FD"
    });
    employess.Add(new Employee {
      Name = "Isabel de Castro", Country = "USA", Address = "Av. del Libertador 900", Salary = 2800.00, Department = "HR"
    });
    employess.Add(new Employee {
      Name = "Bernardo Batista", Country = "BR", Address = "Grenzacherweg 237", Salary = 2700.00, Department = "SD"
    });
return employess;
  }
public string Name {
get;
set;
  }
public string Address {
get;
set;
  }
public string Country {
get;
set;
  }
public double Salary {
get;
set;
  }
public string Department {
get;
set;
  }
}

Basically we want to calculate the TotalSalary distribution based on country.we want the below output. so here we performing GROUPBY and SUM on List items using LINQ.

Linq Query with Group by and Sum in Select

List < Employee > employees = new Employee().Getemployees();
var results = from employee in employees group employee by employee.Country into gp select new {
  Country = gp.Key, TotalSalary = gp.Sum(item => item.Salary)
};
foreach(var r in results) {
  Debug.Print(($ @ "Country:"
"{r.Country}"
",TotalSalary:"
"{r.TotalSalary}"
""));
}

Country:"IN",TotalSalary:"7400"  Country:"USA",TotalSalary:"11600"  Country:"UK",TotalSalary:"5500"  Country:"FR",TotalSalary:"8300"  Country:"BR",TotalSalary:"5900"  

Equivalent SQL queries

SELECT Country,SUM(salary) AS TotalSalary FROM Employee  GROUP BY Country

Group by and Sum in Select with multiple columns

Now let’s say I want to group the list based on the Country and Department, Basically, we want to calculate the TotalSalary distribution based on country and Department. So we are going to use the group by on multiple columns with the Sum aggregate function in the select statement.

We want output like below table

Linq query

var results = from employee in employees group employee by new {
  employee.Country, employee.Department
}
into gp select new {
  Country = gp.Key.Country, Department = gp.Key.Department, TotalSalary = gp.Sum(item => item.Salary)
};
foreach(var r in results) {
  Debug.Print(($ @ ""
"{r.Country}"
" "
"{r.Department}"
" "
"{r.TotalSalary}"
""));
}

Equivalent SQL queries

SELECT Country,Department,SUM(salary) AS TotalSalary FROM TblEmployee  GROUP BY Country,Department

Sql script for table