Reader Level:
ARTICLE

Aggregate Functions in Linq To SQL

Posted by Sandeep Singh Shekhawat Articles | LINQ December 05, 2012
In this article I am going to explain how to use aggregate functions in LINQ to SQL as well as in SQL Server.
  • 1
  • 0
  • 29559

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.

Aggregate-Functions1.jpg

Operation Performing Data

Here I show all the data in the EMPLOYEE table; using:

SELECT * FROM EMPLOYEE

Aggregate-Functions2.jpg

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

Aggregate-Functions3.jpg

SELECT Name,SUM(SALARY) AS [SALARY] FROM EMPLOYEE GROUP BY Name

Aggregate-Functions4.jpg

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));

    }

Aggregate-Functions5.jpg

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

Aggregate-Functions6.jpg

SELECT AVG(SALARY) AS SALARY FROM EMPLOYEE GROUP BY Name

Aggregate-Functions7.jpg

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));

    }

Aggregate-Functions8.jpg

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

Aggregate-Functions9.jpg
 

SELECT Name, COUNT(Id) AS [Total Number] FROM EMPLOYEE GROUP BY Name


Aggregate-Functions10.jpg

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));

    }

Aggregate-Functions11.jpg

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


Aggregate-Functions12.jpg

SELECT Name, MAX(SALARY) AS SALARY FROM EMPLOYEE GROUP BY Name


Aggregate-Functions13.jpg


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));      

 

    }

Aggregate-Functions14.jpg

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


Aggregate-Functions15.jpg

SELECT Name, MIN(SALARY) AS SALARY FROM EMPLOYEE GROUP BY Name


Aggregate-Functions16.jpg

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));      

 

    }

Aggregate-Functions17.jpg

COMMENT USING

Trending up