SQL Server Group Functions

SQL group functions are also called as Aggregate functions or Multi row functions. This functions applied on multiple records and returns single value.

Reference table used for all the examples in this article

CREATE TABLE [dbo].[emp](
[empname] [varchar](20) Not NULL,
[empno] [numeric](18, 0) NULL,
[job] [varchar](30) NULL,
[joindate] [date] NULL,
[salary] [money] NULL,
[deptno] [nchar](10) NULL,
)

Emp-table

MAX: Returns the maximum value of a group expression.

Examples:

1) Display the maximum salary of employees

select max(salary) from emp

2) Display the maximum salary of department number 30.

select max(salary from emp where deptno=10

Output: 3000

MIN: Returns the minimum value of a given expression.
Examples:

Display the minimum salary of employees

select min(salary) from emp

Output: 10000

SUM: This function returns total values of a given expression. This can be applied only numeric columns.

Examples:

Display the total salary paid to all managers

select sum(salary) from emp where job=‘manager’

Output: 15500

AVG: This function returns average value of given expression.

Examples:

Display the average salary paid to all employees

select avg(salary) from emp

Output: 6750

COUNT: This function returns the number of values present in a column
Examples:

Display the total number of employees

select count(empno) from emp

Output: 8

COUNT(*): This function returns the number of values present in a table.
Examples:

Display the total number of records from table employee

select count(*) from emp

Output: 8

Powered by k2schools