SQL Server Aggregate Functions

Employee Table

Let us consider the example 'Employee' database table.

SQL Server AVG Function

In SQL Server, AVG function is used to calculate average value based on numeric column, constants or expression.

AVG Function Syntax

SELECT AVG([ALL | DISTINCT] expression)
FROM table_name
WHERE Filter_condition

AVG Function Example

Computing average salary in SELECT Statement using AVG function.

SELECT AVG(Salary) 'Average Salary'
FROM Employee

AVG Function with DISTINCT

Computing average salary in SELECT Statement using AVG function with DISTINCT Keyword.

SELECT AVG(DISTINCT Salary) 'Average Salary'
FROM Employee

AVG Function with ALL

Computing average salary in SELECT Statement using AVG function with ALL Keyword.

SELECT AVG(ALL Salary) 'Average Salary'
FROM Employee

SQL Server COUNT Function

In SQL Server, COUNT function is used to get number of rows and parameter can be *, numeric column, constants or expression.

COUNT Function Syntax

SELECT COUNT ([ALL | DISTINCT] expression)
FROM table_name
WHERE Filter_condition

COUNT Function Example

Computing number of rows based on all the columns from Employee table in SELECT Statement using COUNT function.

SELECT COUNT(*) 'Rows Count'
FROM Employee

Computing number of rows based on Id column from Employee table in SELECT Statement using COUNT function.

SELECT COUNT(Id) 'Rows Count'
FROM Employee

COUNT Function with DISTINCT

Computing number of rows based on Salary column from Employee table in SELECT Statement using COUNT function.

SELECT COUNT(DISTINCT Salary) 'Rows Count'
FROM Employee

COUNT Function with ALL

Computing number of rows based on Salary column from Employee table in SELECT Statement using COUNT function.

SELECT COUNT(ALLSalary) 'Rows Count'
FROM Employee

SQL Server MAX Function

In SQL Server, MAX function is used to get the maximum value based on column or expression.

MAX Function Syntax

SELECT MAX (expression)
FROM table_name
WHERE Filter_condition

MAX Function Example

Computing maximum value based on column or expression from Employee table in SELECT Statement using MAX function.

SELECT MAX(Salary) 'Maximum Salary'
FROM Employee

SQL Server MIN Function

In SQL Server, MIN function is used to get the minimum value based on column or expression.

MIN Function Syntax

SELECT MIN(expression)
FROM table_name
WHERE Filter_condition

MIN Function Example

Computing minimum value based on column or expression from Employee table in SELECT Statement using MIN function.

SELECT MIN(Salary) 'Minimum Salary'
FROM Employee

SQL Server SUM Function

In SQL Server, SUM function is used to calculate sum value based on numeric column, constants or expression.

SUM Function Syntax

SELECT SUM([ALL | DISTINCT] expression)
FROM table_name
WHERE Filter_condition

SUM Function Example

Computing sum salary in SELECT Statement using SUM function.

SELECT SUM(Salary) 'Total Salary'
FROM Employee

SUM Function with DISTINCT

Computing sum of salary's in SELECT Statement using SUM function with DISTINCT Keyword.

SELECT SUM(DISTINCT Salary) 'Total Salary'
FROM Employee
Output:



SUM Function with ALL
Computing average salary in SELECT Statement using SUM function with ALL Keyword.
SELECT SUM(ALL Salary) 'Total Salary'
FROM Employee
Output:





« Previous
Next »