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:"

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"  
select new { Country = gp.Key, TotalSalary = gp.Sum(item => item.Salary) }: This part of the query selects the result projection. It creates a new anonymous type with two properties:

Country: This property stores the key of the group, which is the Country value.
TotalSalary: This property calculates the sum of the Salary for all Employee objects in the group using the Sum function,the above query groups the Employee objects by their country and calculates the total salary for each country. The result is stored in the results variable as an enumerable collection of anonymous objects with Country and TotalSalary properties.

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

Country Department TotalSalary
IN HR 5000
USA IT 2500
UK FD 2000
FR FD 8300
UK SD 3500
BR FD 3200
USA HR 5600
IN SD 2400
USA SD 3500
BR SD 2700

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}"
""));
}
select new { Country = gp.Key.Country, Department = gp.Key.Department, TotalSalary = gp.Sum(item => item.Salary) }- This part of the query selects the result. It creates a new anonymous type with three properties:
Country: This property stores the Country value extracted from the composite key.
Department: This property stores the Department value extracted from the composite key.
TotalSalary: This property calculates the sum of the Salary for all Employee objects in the group using the Sum function.
The query groups the Employee objects by a combination of their country and department, and then calculates the total salary for each unique combination. The result is stored in the results variable as an enumerable collection of anonymous objects with Country, Department, and TotalSalary properties.

Equivalent SQL queries

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

Sql script for table

CREATE TABLE [dbo].[employee]
             (
                          [id] [INT] IDENTITY(1,1) NOT NULL,
                          [name] [NVARCHAR](max) NULL,
                          [address] [NVARCHAR](max) NULL,
                          [country] [NVARCHAR](max) NULL,
                          [salary] [DECIMAL](18, 2) NOT NULL,
                          [department] [NVARCHAR](max) NULL,
                          CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ( [id] ASC )
             )
ON [PRIMARY] textimage_on [PRIMARY]goSET IDENTITY_INSERT [dbo].[Employee] ONgoINSERT [dbo].[employee]
       (
              [id],
              [name],
              [address],
              [country],
              [salary],
              [department]
       )
       VALUES
       (
              1,
              'Du monde entier',
              '67, rue des Cinquante Otages',
              'IN',
              Cast(5000.00 AS DECIMAL(18, 2)),
              'HR'
       )goINSERT [dbo].[employee]
       (
              [id],
              [name],
              [address],
              [country],
              [salary],
              [department]
       )
       VALUES
       (
              2,
              'Chop-suey Chinese ',
              'Hauptstr. 29',
              'USA',
              Cast(2500.00 AS DECIMAL(18, 2)),
              'IT'
       )goINSERT [dbo].[employee]
       (
              [id],
              [name],
              [address],
              [country],
              [salary],
              [department]
       )
       VALUES
       (
              3,
              'France restauration',
              '54, rue Royale',
              'UK',
              Cast(2000.00 AS DECIMAL(18, 2)),
              'FD'
       )goINSERT [dbo].[employee]
       (
              [id],
              [name],
              [address],
              [country],
              [salary],
              [department]
       )
       VALUES
       (
              4,
              'Howard Snyder',
              '2732 Baker Blvd.',
              'FR',
              Cast(2400.00 AS DECIMAL(18, 2)),
              'FD'
       )goINSERT [dbo].[employee]
       (
              [id],
              [name],
              [address],
              [country],
              [salary],
              [department]
       )
       VALUES
       (
              5,
              ' Carlos Hernández',
              'Carrera 22 con Ave. Carlos Soublette #8-35',
              'UK',
              Cast(3500.00 AS DECIMAL(18, 2)),
              'SD'
       )goINSERT [dbo].[employee]
       (
              [id],
              [name],
              [address],
              [country],
              [salary],
              [department]
       )
       VALUES
       (
              6,
              'France restauration',
              '54, rue Royale',
              'BR',
              Cast(3200.00 AS DECIMAL(18, 2)),
              'FD'
       )goINSERT [dbo].[employee]
       (
              [id],
              [name],
              [address],
              [country],
              [salary],
              [department]
       )
       VALUES
       (
              7,
              'John Steel ',
              '2732 Baker Blvd.',
              'USA',
              Cast(2800.00 AS DECIMAL(18, 2)),
              'HR'
       )goINSERT [dbo].[employee]
       (
              [id],
              [name],
              [address],
              [country],
              [salary],
              [department]
       )
       VALUES
       (
              8,
              'Renate Messner ',
              'Carrera 22 con Ave. Carlos Soublette #8-35',
              'FR',
              Cast(2700.00 AS DECIMAL(18, 2)),
              'FD'
       )goINSERT [dbo].[employee]
       (
              [id],
              [name],
              [address],
              [country],
              [salary],
              [department]
       )
       VALUES
       (
              9,
              'Horst Kloss',
              'Rua da Panificadora, 12',
              'IN',
              Cast(2400.00 AS DECIMAL(18, 2)),
              'SD'
       )goINSERT [dbo].[employee]
       (
              [id],
              [name],
              [address],
              [country],
              [salary],
              [department]
       )
       VALUES
       (
              10,
              'Guillermo Fernández',
              'Carrera 22 con Ave. Carlos Soublette #8-35',
              'USA',
              Cast(3500.00 AS DECIMAL(18, 2)),
              'SD'
       )goINSERT [dbo].[employee]
       (
              [id],
              [name],
              [address],
              [country],
              [salary],
              [department]
       )
       VALUES
       (
              11,
              'Georg Pipps',
              '54, rue Royale',
              'FR',
              Cast(3200.00 AS DECIMAL(18, 2)),
              'FD'
       )goINSERT [dbo].[employee]
       (
              [id],
              [name],
              [address],
              [country],
              [salary],
              [department]
       )
       VALUES
       (
              12,
              'Isabel de Castro',
              'Av. del Libertador 900',
              'USA',
              Cast(2800.00 AS DECIMAL(18, 2)),
              'HR'
       )goINSERT [dbo].[employee]
       (
              [id],
              [name],
              [address],
              [country],
              [salary],
              [department]
       )
       VALUES
       (
              13,
              'Bernardo Batista',
              'Grenzacherweg 237',
              'BR',
              Cast(2700.00 AS DECIMAL(18, 2)),
              'SD'
       )goSET IDENTITY_INSERT [dbo].[Employee] OFFgo