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#
- "Value Cannot Be Null" when using LINQ to SQL to Group By and Sum results from a WCF Service request
- Linq SQL join on same table with group and sum
- Linq to SQL - how to make a select with conditions and return IQueryable
- Group by with Sum and joined values Linq to Object
- SQL server: insert into using select and other parameter
- Delaying LINQ to SQL Select Query Execution
- Linq to entity select and count?
- SQL query to get the sum and the latest enries
- Using Having Sum with Linq to SQL
- update linq to sql values with select new keyword
- c# linq how to declare in SQL Server and save date and time separately
- How to do a Select and Update query on one SQL string?
- Custom select parameters from linq to sql
- split value and search in linq to sql
- How to select column names and tables of an SQL using regex?
- Linq to SQL joining two tables and populate GridView
- Linq to Sql - Select from 1:Many
- Can't select properties after group by in linq query
- SUM and Group after Right Join two Tables
- how to control the order of the Childs added and submitted to a parent in Linq to sql in C#?
- How can i write sum query with linq to sql to calculate sum of the nchar field value?
- Datareader not working with Select Upper and Group By Upper
- Execute a SQL SELECT query and display the results in a GridView on button click
- Which is faster between Linq to Sql And SQl Query
- Comparison of Linq to SQL and Stored procedures in terms of performance
- WPF ListView with group of RadioButtons and select default value
- LINQ to SQL and dynamic query on Windows Phone
- SQL to LINQ group by
- Group by name and Include duplicate data in linq to xml c#
- Linq To Sql Nested Select
- How to get Number of shares and Likes for a URL using FacebookGraph API
- Caliburn Micro MVVM: Handling data exchange from one View/ViewModel to other ones
- string.IsNullOrEmpty() method not handling session variables properly
- How can I create a string of random characters (as for a password...)?
- Load Assembly into AppDomain from App.Config
- Implicit wait Selenium
- NModbus4 TcpListener doesn't start with a valid IP Address
- C# how to create while loop to validate if object is null
- Overriding events of a base event to an application and sequence of flow c#
- page refresh only when new records are inserted in database
- abstract database away for dynamically generated sql in C#
- Datagridview date to label formatting issue
- How to set ViewModel Properties from DataGrid Selected Row using Caliburn Micro
- Good practices for an object that will be accessed & updated in many classes in an application?
- ListBox not updating after deserialization
- C# with command line FTP help
- Force a class to implement an enum with no value constrain
- Character jumping randomly while holding jump button?
- create dictionary with arguments
- UWP Converting of an IF statement to foreach loop for NavigationViewItems