SQL Aggregate Functions

The SQL Aggregate Functions are functions that provide mathematical operations. The functions include:

• count() - counts a number of rows
• sum() - compute sum
• avg() - compute average
• min() - compute minimum
• max() - compute maximum

SQL Aggregate Functions are used as follows. If a grouping of values is needed also include the GROUP BY clause.

Use a column name or expression as the parameter to the Aggregate Function. The parameter, '*', represents all rows.

SQL Aggregate Functions Syntax

SELECT <column_name1>, <column_name2> <aggregate_function(s)> FROM <table_name> GROUP BY <column_name1>, <column_name2>

SQL Aggregate Functions Example

The following example Aggregate Functions are applied to the employee_count of the branch table. The region_nbr is the level of grouping.

Here are the contents of the table:

Table: BRANCH

branch_nbr	branch_name	region_nbr	employee_count
108	New York	100	10
110	Boston	100	6
212	Chicago	200	5
404	San Diego	400	6
415	San Jose	400	3

This SQL Statement with aggregate functions is executed:

SELECT region_nbr, count(branch_nbr), sum(employee_count), min(employee_count), max(employee_count), avg(employee_count) FROM dbo.branch GROUP BY region_nbr ORDER BY region_nbr

Here is the result.

region_nbr	count (branch_nbr)	sum (employee_count)	min (employee_count)	max (employee_count)	avg (employee_count)
100	2	16	6	10	8
200	1	5	5	5	5
400	2	9	3	6	4