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

In this article, we will learn Linq to SQL Group by and Sum in Select using C#. We have already learned How to use group by in SQL query? Now in this post we will perform the Group by and Sum in Select statement using LINQ query.

I’m assuming that you are familiar with SQL queries and group by clause If not then please go through 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}"""));
            }

Output

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

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]
GO
SET IDENTITY_INSERT [dbo].[Employee] ON 
GO
INSERT [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')
GO
INSERT [dbo].[Employee] ([Id], [Name], [Address], [Country], [Salary], [Department]) VALUES (2, 'Chop-suey Chinese	', 'Hauptstr. 29', 'USA', CAST(2500.00 AS Decimal(18, 2)), 'IT')
GO
INSERT [dbo].[Employee] ([Id], [Name], [Address], [Country], [Salary], [Department]) VALUES (3, 'France restauration', '54, rue Royale', 'UK', CAST(2000.00 AS Decimal(18, 2)), 'FD')
GO
INSERT [dbo].[Employee] ([Id], [Name], [Address], [Country], [Salary], [Department]) VALUES (4, 'Howard Snyder', '2732 Baker Blvd.', 'FR', CAST(2400.00 AS Decimal(18, 2)), 'FD')
GO
INSERT [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')
GO
INSERT [dbo].[Employee] ([Id], [Name], [Address], [Country], [Salary], [Department]) VALUES (6, 'France restauration', '54, rue Royale', 'BR', CAST(3200.00 AS Decimal(18, 2)), 'FD')
GO
INSERT [dbo].[Employee] ([Id], [Name], [Address], [Country], [Salary], [Department]) VALUES (7, 'John Steel	', '2732 Baker Blvd.', 'USA', CAST(2800.00 AS Decimal(18, 2)), 'HR')
GO
INSERT [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')
GO
INSERT [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')
GO
INSERT [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')
GO
INSERT [dbo].[Employee] ([Id], [Name], [Address], [Country], [Salary], [Department]) VALUES (11, 'Georg Pipps', '54, rue Royale', 'FR', CAST(3200.00 AS Decimal(18, 2)), 'FD')
GO
INSERT [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')
GO
INSERT [dbo].[Employee] ([Id], [Name], [Address], [Country], [Salary], [Department]) VALUES (13, 'Bernardo Batista', 'Grenzacherweg 237', 'BR', CAST(2700.00 AS Decimal(18, 2)), 'SD')
GO
SET IDENTITY_INSERT [dbo].[Employee] OFF
GO

The post [Simple Way]-Cascading DropDownList in Asp.Net Mvc Using Jquery Ajax appeared first on Software Development | Programming Tutorials.