In this article I am going to explain how to use aggregate functions in LINQ to SQL as well as in SQL Server.
Aggregate Function
An aggregate function is a function where the values of multiple rows are grouped together as input on certain criteria to form a single value of greater significance or measurement such as a set. Aggregate functions return a single value.
Common Aggregate Functions are:
- SUM() : Returns the sum of column values.
- AVERAGE() : Returns the average of column values.
- COUNT() : Returns the total number of rows in a table.
- MAX() : Returns the maximum value in the column.
- MIN() : Returns the minimum value in the column.
Create DataContext Class
I create a data context class to perform aggregate function operations. So first drag and drop the EMPLOYEE table to the data context class.
Operation Performing Data
Here I show all the data in the EMPLOYEE table; using:
SELECT * FROM EMPLOYEE
Use GridView Control
Here I am using a GridView control to show employee data:
<asp:GridView ID="grdEmployee" runat="server"></asp:GridView>
SUM() Function
This SUM() function returns a single value that is the result of adding all row's values for a single column or can specify a certain criteria. The SQL SUM() function takes an argument specifying which column to add all values of every row for, or some criteria for a column. In the following code I am showing the sum of salaries for an individual employee and all employees.
1. In SQL Server
SELECT SUM(SALARY) AS [TOTAL SALARY] FROM EMPLOYEE
SELECT Name,SUM(SALARY) AS [SALARY] FROM EMPLOYEE GROUP BY Name
2. In LINQ To SQL
private void SumGroupSalary()
{
EmployeeOperationDataContext employee = new EmployeeOperationDataContext();
/* Addition of salary by employee */
var salaryEmpSum = from emp in employee.EMPLOYEEs
group emp by emp.Name into empg
select new
{
Name = empg.Key,
Salary = empg.Sum(x => x.SALARY)
};
grdEmployee.DataSource = salaryEmpSum;
grdEmployee.DataBind();
/*Total Salaries for all employee */
var salarySum = (from emp in employee.EMPLOYEEs
select emp.SALARY).Sum();
Response.Write(string.Format("Addition of Salary is : {0} ", salarySum));
}
AVERAGE() Function
This AVERAGE() function returns a single value that is the average of all row's values for a single column or can specify a criteria for a single column. The SQL AVG() function takes an argument specifying which column to average all values of every row for, or some criteria for a column. In the following code I am showing the average of salaries for an individual employee and all employees.
1. In SQL Server
SELECT AVG(SALARY) AS SALARY FROM EMPLOYEE 
SELECT AVG(SALARY) AS SALARY FROM EMPLOYEE GROUP BY Name
2. In LINQ To SQL
private void AverageSalary()
{
EmployeeOperationDataContext employee = new EmployeeOperationDataContext();
/*Averge salary from multiple salries for employee */
var salaryGroupAvg = from emp in employee.EMPLOYEEs
group emp by emp.Name into empg
select new
{
Name = empg.Key,
Salary = empg.Average(x => x.SALARY)
};
grdEmployee.DataSource = salaryGroupAvg;
grdEmployee.DataBind();
/*Averge of whole salary in Employee table*/
var salaryAvg = (from emp in employee.EMPLOYEEs
select emp.SALARY).Average();
Response.Write(string.Format("Average of Salary is : {0} ", salaryAvg));
}
COUNT() Function
The COUNT() function returns a single value that is the count of all rows for a single column or can specify a criteria for a single column. The SQL COUNT() function takes an argument that represents which column will be used to count the total rows for or some criteria for a column. In the following code I am showing the total number employees with the same employee name and the total number of employees.
1. In SQL Server
SELECT COUNT(Id) AS [Total Number] FROM EMPLOYEE
SELECT Name, COUNT(Id) AS [Total Number] FROM EMPLOYEE GROUP BY Name
2. In LINQ To SQL
private void TotalNumberOfEmployee()
{
EmployeeOperationDataContext employee = new EmployeeOperationDataContext();
var totalEmployee = from emp in employee.EMPLOYEEs
group emp by emp.Name into empg
select new
{
Name = empg.Key,
TotalEmployee = empg.Count()
};
grdEmployee.DataSource = totalEmployee;
grdEmployee.DataBind();
var employeeCount = (from emp in employee.EMPLOYEEs
select emp.Id).Count();
Response.Write(string.Format("Total number of Employee is : {0} ", employeeCount));
}
MAX() Function
This MAX() function returns a single value that is the maximum of all rows for a single column or can specify a criteria for a single column. The SQL MAX() function takes an argument specifying a column to get the maximum value from, or some criteria for a column. In the following code I am showing the maximum salary for an individual employee and all employees.
1. In SQL Server
SELECT MAX(SALARY) AS SALARY FROM EMPLOYEE
SELECT Name, MAX(SALARY) AS SALARY FROM EMPLOYEE GROUP BY Name
2. In LINQ To SQL
private void MaxSalary()
{
EmployeeOperationDataContext employee = new EmployeeOperationDataContext();
var salaryMaximum = from emp in employee.EMPLOYEEs
group emp by emp.Name into empg
select new
{
Name = empg.Key,
MaximumSalary = empg.Max(x => x.SALARY)
};
grdEmployee.DataSource = salaryMaximum;
grdEmployee.DataBind();
var salaryMax = (from emp in employee.EMPLOYEEs
select emp.SALARY).Max();
Response.Write(string.Format("Maximun Salary is : {0} ", salaryMax));
}
MIN() Function
This MIN() function returns a single value that is the minimum in all rows for a single column or can be from a specified criteria for a single column. The SQL MIN() function takes a argument that specifies a column to get the minimum value for from all rows or can be a specified criteria. In the following code I am showing the minimum salary for an individual employee and all employees.
1. In SQL Server
SELECT MIN(SALARY) AS SALARY FROM EMPLOYEE
SELECT Name, MIN(SALARY) AS SALARY FROM EMPLOYEE GROUP BY Name
2. In LINQ To SQL
private void MinSalary()
{
EmployeeOperationDataContext employee = new EmployeeOperationDataContext();
/*Minimum salary per employee name */
var salaryMinimum = from emp in employee.EMPLOYEEs
group emp by emp.Name into empg
select new
{
Name = empg.Key,
MinimumSalary = empg.Min(x => x.SALARY)
};
grdEmployee.DataSource = salaryMinimum;
grdEmployee.DataBind();
/*Minimum salary in all employees */
var salaryMin = (from emp in employee.EMPLOYEEs
select emp.SALARY).Min();
Response.Write(string.Format("Minimun Salary is : {0} ", salaryMin));
}
