SQL Server Functions with Syntax and Examples

In this tutorial, we will study SQL Server Functions in detail.

We have various functions in SQL some are specifically for strings, some are for numeric data types and likewise. Let us now dive deep into the SQL functions with syntax and examples all the way long.

What are SQL Server Functions?

In SQL, we have some built-in functions which help us to perform the specified tasks.

These built-in functions help us to attain required results in avoiding complex queries and saving time in writing out large messy queries.

In SQL, we have two basic bifurcations of built-in functions:

1. SQL Aggregate Functions

These functions help us to perform arithmetic operations on our attributes. These functions after operating return single values.

Examples:

• SUM
• AVG
• MAX
• MIN etc.

2. SQL Scalar Functions

These functions are performed on string and DateTime type attributes. These functions also return single values upon operating on the given attribute.

Examples:

• UCASE
• DAY
• YEAR
• LTRIM etc.

Types of SQL Server Functions

The divisions of functions can be made on various grounds but the most important one is based on the datatypes on which these functions can be applied.

We have three major bifurcations:

1. Aggregate Functions
2. String Functions
3. DateTime Functions

In this tutorial, we will be focussing on the Aggregate Functions as we have already covered the String and DateTime functions in the previous tutorials.

SQL Demo Database

Let us now have a look at our demo database – DataFlair.
Query:

USE DataFlair;
SELECT * FROM DataFlair;

Output:

SQL Aggregate Functions

Aggregate functions are mathematical functions that are applied to the attributes in a database to get results. They help us to perform various mathematical and statistical operations on data without any additional efforts.

We will now discuss each of the Aggregate functions in detail.

1. SQL AVG()

Allows us to find the average of any numerical attribute in our database.
Syntax:

SELECT AVG(col1) as average_val , col2, col3…..
FROM tableName;

Example: Let us now try to find the average experience of employees at DataFlair.
Query:

SELECT AVG(experience) AS Avg_Experience FROM DataFlair;

Output:

2. MIN() IN SQL

Allows us to find the minimum value of any numerical attribute without any additional efforts.
Syntax:

SELECT MIN(col1) as min_val , col2, col3…..
FROM tableName;

Example: Let us now try to find the employee with the minimum experience in DataFlair.
Query: 

SELECT name, MIN(experience) as Exp, location FROM Dataflair;

Output:

3. MAX() in SQL

Helps us to find the max value of the numerical attributes in the database.
Syntax:

SELECT MAX(col1) as max_val , col2, col3…..
FROM tableName;

Example: Let us now try to find the employee with the maximum experience in DataFlair.
Query: 

SELECT name, MAX(experience) as Exp, location FROM Dataflair;

Output:

4. SUM() in SQL

Allows us to sum the numerical attributes in a column.
Syntax:

SELECT SUM(col1) as SUM_val , col2, col3…..
FROM tableName;

Example: Let us now see what is the total experience of all employees when cumulated.
Query:

SELECT SUM(experience) as Total_Exp FROM Dataflair;

Output:

5. COUNT() in SQL

Allows us to count the number of records in a particular attribute.

It is more popularly used with the DISTINCT keyword to find the number of unique records in the database.
Syntax:

SELECT COUNT(col1) as count_val , col2, col3…..
FROM tableName;

OR

SELECT COUNT(DISTINCT(col1)) as count_val , col2, col3…..
FROM tableName;

Example 1: Let us first see the total number of employees in our DataFlair database.
Query: 

SELECT COUNT(emp_id) AS total_employees FROM DataFlair;

Output:

Example 2: Let us now find the number of unique names in our DataFlair database.
Query:

SELECT COUNT(DISTINCT(emp_id)) AS Unique_Names FROM DataFlair;

Output:

Summary

In this tutorial, we have seen in detail the server functions of SQL.

We have seen that they are divided into various bifurcations based on various parameters like the number of output and the data types on which they operate.

Based on the outputs, they are divided into Scalar and Aggregate functions.

When we talk about the data types on which they operate we can classify them into numeric functions, string functions, and DateTime functions. We have then seen in detail all the aggregate functions.