SQL Server Aggregate Functions

Main Article :- Sql Server AVG() Function

The AVG() function in SQL Server returns an expression's average value.

It is important to note that NULL values are ignored.

Syntax:

The AVG() function in SQL Server has the following syntax:

AVG(aggregate_expression)

Parameter Values

Technical Details

Examples:-

Example : Pick all the books that are more expensive than the average:

Let's take BOOKSIMPLE table to execute the query:

The query will be look like this,

SELECT * FROM EMPLOYEESIMPLE
WHERE Salary > (SELECT AVG(Salary) FROM EMPLOYEESIMPLE);

Result:

Pick all the all columns in employee simple table who's salary is more expensive than the average.

Main Article :- Sql Server COUNT() Function

COUNT() returns the number of records returned from a select query.

The only difference between these functions is the data types of their return values. Count always generates an output of type int.

Note: NULL values are not counted.

Syntax:

The COUNT() function in SQL Server has the following syntax:

COUNT(expr)

Parameter Values

Technical Details

Examples:-

Example 1: The number of books in the "BOOKNAME" column should be returned:

Let's take BOOKSIMPLE table to execute following query:

The query will be look like this,

SELECT COUNT(BookName) AS NumberOfBooks FROM BOOKSIMPLE;

Result:

This output is used to count all the books in the BOOKNAME Column.

Example 2: Let's check with some NULL values in the Domain column:

Let's take BOOKADVANCE table to execute following query:

The query will be look this,

SELECT COUNT(domain) AS NumberOfDomain FROM BOOKADVANCE;

Result:

This result counts the Domain column calculate 2 because 2 rows in this column are empty.

Main Article :- Sql Server MAX() Function

In a list of values, the MAX() method returns the highest value.

Syntax:

The MAX() function in SQL Server has the following syntax:

SELECT MAX(aggregate_expr)
FROM tables;

Parameter Values

Technical Details

Examples:-

Example: The highest (maximum) salary is returned in the following example:

Let's take EMPLOYEESIMPLE table to execute the query:

The query will be look like this,

SELECT MAX(Salary) AS HighestSalary FROM EMPLOYEESIMPLE;

Result:

From the result, HighestSalary 38500 return from the Salary column in the EMPLOYEESIMPLE table will be returned.

Main Article :- Sql Server MIN() Function

In a list of values, the MIN() method returns the smallest value.

Syntax:

The MIN() function in SQL Server has the following syntax:

SELECT MIN(aggregate_expr)
FROM tables;

Parameter Values

Technical Details

Examples:-

Example: The smallest (minimum) salary is returned in the following example:

Let's take EMPLOYEESIMPLE table to execute the query:

The query will be look like this,

SELECT MIN(Salary) AS SmallestSalary FROM EMPLOYEESIMPLE;

Result:

From the result, SmallestSalary 7000 return from the Salary column in the EMPLOYEESIMPLE table will be returned.

Main Article :- Sql Server SUM() Function

The SUM() function returns the expression's total value.

Note: The NULL values are not taken into account.

Syntax:

The SUM() function in SQL Server has the following syntax:

SUM(expr)

Parameter Values

Technical Details

Examples:-

Example 1: In the "BOOKSIMPLE" table, return the total of the "Price" column:

Let's look

The query will be look like this,

SELECT SUM(Price) AS SumOfPrice FROM BOOKSIMPLE;

Result:

The sum of price in the price column is 340.00.