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:
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
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}"""));
}
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.
Read More Articles
- Write a value which contain comma to a CSV file in c#?
- Reading CSV File with cells containing commas c#
- Split CSV with columns may contain ‘,’ Comma C#
- [Simple Way]-Cascading DropDownList in Asp.Net Mvc Using Jquery Ajax
- [Simple Way]-How to get data from database using JQuery Ajax in asp net MVC
- [Simple Way]-ASP.NET Core Upload Multiple File Web API model
- [Simple Way]- Image Upload in .NET Core Web API
- [Easy Way]-Receive File and other form data together in ASP.NET Core Web API
- Replace image in word document using C#
- How to add new rows to an existing word document table in C#
- LINQ to SQL Join and Sum
- I want to integrate select count(*) and distinct query to my sql command which include group by and where condition?
- How to get All select keys in Linq Group By Clause
- c# lambda reading each row with GROUP BY and SUM
- Linq to SQL simple SUM
- Parse full name into First Name and Last Name from SQL Server - Linq to Entities
- Left Outer Join and Exists in Linq To SQL C# .NET 3.5
- Linq to SQL and using ToList, how many DB calls are made?
- Select TOP row in SQL and get 5 rows of end table
- SQL Server select and insert issue
- How to make an anonymous type property nullable in the select of a linq to sql query
- LINQ to SQL Group by C# problem
- How to perform Linq select new with datetime in SQL 2008
- LINQ to sql return group by results from method
- Linq to SQL, SQL Server 2008 and optimization
- Best way to use LINQ to SQL Contains() between a local List and a table
- How to iterate through SQL select statement results and update a specific table
- C# and SQL SELECT
- LINQ to SQL .Count takes way to much process time and decreases performances
- How to select Distinct char data type and convert into int by casting using sql
- Sum Columns from joined tables to get a calculated value using Linq to SQL
- C# SQL table select and count distinct values
- select from union select with order by and group by in mongodb using aggregation
- select records with max aggregate using Linq To SQL
- Should I be using Data Access and Business Logic inside my BLL class when using Linq To SQL
- LINQ to SQL in C# > Updates and inserts not working. Table has PK
- Select with linq two object from SQL Server database
- Linq to sql select records based on child records
- LINQ to SQL combining read uncommitted and read committed
- LINQ to Sql is empty and throws Object reference not set to an instance of an object. error
- Converting very large files from xml to csv
- ASP.NET Core 5 Web API with Entity Framework works when running in IDE but not when published to folder and installed in IIS
- Process start from two strings
- Parse json in c# when each name/value pair isn't sent
- A simple way to convert a static class to regular class in legacy applications to enable mocking
- LongListSelector on WP8 linq-to-sql binding issue
- Is there a way to programmatically determine the GPS Coordinate which should serve as the Center of a Bing Map?
- Asynch operation in WinForm app
- Binding MVVM Command to TimePicker control in UWP
- C# Soft Number By Difference From Input
- Unity 3D, transform.position not working on instatiate object
- How do I merge (or dynamically build and combine) lambda expressions in C#?
- WPF resizing, * vs Auto
- how to SQL SELECT & loop through each row of results with code?
- Why doesn't C# allow for global inferred types i.e. using var?
- Dynamically add properties to a class
- LoginController not being called in ASP.Net Core MVC (In Visual Studio Code)
- How to set Listbox DisplayMember and ValueMember using fields of an object?
- OxyPlot graphic doesn't update
- MVVM: Update view's control visibility after completion of threadpool worker item in WPF NET 3.5