Boost Performance and Reduce Code Use With SQL Server Aggregate Functions

3. AVG (column)

The AVG function returns the average value of a selected column. It does not include NULL values in the calculation.

Syntax:

SELECT AVG (column) FROM Table

Example:

SELECT AVG (salary) FROM Employee

This example returns the average age for persons older than 30.

Result: 16240.90.

4. COUNT (Column)

The COUNT function returns the number of not NULL items in a group.

Syntax:

SELECT COUNT (column) FROM Table

Example:

SELECT COUNT (Age) FROM Employee Where salary > 15000

This example finds the number of persons with a value in the "Age" field in the "Persons" table.

Result: 7.

5. SUM (column)

The SUM function returns total sum of a selected column. It does not include NULL values in the calculation.

Syntax:

SELECT SUM (column) FROM Table

Example:

SELECT SUM (Salary) FROM Employee

This example returns the sum of all ages in the "person" table.

Result: 178650.

6. FIRST (column)

The FIRST function returns the value of the first record in the selected field.

Syntax:

SELECT FIRST (column) AS [expression] FROM table

Example:

SELECT FIRST (Age) AS lowest age
FROM Employee
ORDER BY Age

Result: 19.

7. LAST (column)

The LAST function returns the value of the last record in the selected field.

Syntax:

SELECT FIRST (column) AS [expression] FROM table

Example:

SELECT LAST (Age) AS highest age
FROM Employee
ORDER BY Age

Result: 45.

These functions are vital, as they reduce the number of lines in your code and enable you to perform complex SQL queries with ease. These functions operate on an entire group, table, or row, or a set, and significantly increase the scope of the language in terms of data manipulation, and by serving disparate user needs.

Join a discussion about this article in our forum.