SQL Functions: Aggregate and Scalar Functions with Examples

• Introduction
• What are SQL Functions?
• Aggregate Functions in SQL
• Scalar Functions in SQL

Introduction 

SQL stands for Structured Query Language which is mainly used to handle the data stored in databases. SQL provides various built-in functions and queries which are very useful to access the data. These queries make our work easier to fetch the required data from databases. You can use SQL functions to evaluate various queries for the data and get desired outputs. This helps in fetching the specific data from databases. 

As we know SQL is a declarative language where we need to tell the system what kind of output we need instead of explicitly computing the output like in other programming languages. There are various SQL queries that are structured in such a way that the interpreter changes them in plain language for example: “Get this type of data from this location with these parameters”, or “Apply these calculations on the data found from this location and return the output”. This way the query is sent to the database management system of the language. Now, it decides the best way to calculate the data found and return it as the desired output. 

The functions in SQL are a set of statements that perform specific tasks. You can provide various arguments in these functions and perform different calculations for desired results. It should be noted that a function always returns something whether it is a single value or a whole table. This means the purpose of a function is to make the query tasks easy and give results. We can also create our custom functions and use them multiple times whenever needed. 

This article is all about SQL functions and queries. The functions and queries of SQL increase great productivity at work and helps in various calculations of the data. The SQL queries and functions are not like other programming languages where we define our functions and use them. Here most of the SQL functions are built-in and just need to be used. Hence, for specific functions, we can also define them and use them whenever we need them. Here we will discuss the useful functions of SQL and some queries of these functions. 

What are SQL Functions?

SQL is an abbreviation used for Structured Query Language which is a standardized language for adding, deleting, and modification of the data stored in databases. SQL functions are built-in functions of SQL that helps in retrieving desired results on performing various calculations. The built-in functions of SQL are useful for performing mathematical calculations and concatenation of strings. 

Tables in SQL are a form of related data that are grouped such that each column works as a key or value. This key is used to identify any specific row or column and the types of data are stored along with the row.

There is more type of functions in those databases that use SQL having Data Manipulation Language (DML), Data Definition Language (DDL), and Data Query Language (DQL) functions. 

The DDL functions are useful when we want to manipulate the data and transform it in a useful manner within the database. Such that we don’t need to extract or pull out the data from the database to transform or manipulate it. We can do all the operations within the database using DML functions. 

DDL functions are useful when we want to define the schema or the structural aspects of the database using queries. As DDL means defining the data, therefore, the programmer defines the data that is needed from the database. Such that only that data will be extracted from the databases. 

DQL is a data query language used to query the data in order to extract only specific data from the databases. The returned data is based on some restrictions that are provided by the programmer. This way the specific data can be extracted from the databases using a data query language.

There are also some other functions of SQL, i.e. 

1. Aggregate Functions: Aggregate functions are used across values in a column.

2. Scalar Functions: Scalar functions are used on the values that give as input to the function. 

These functions return single values when we perform various mathematical operations on the data. Let us discuss these functions in detail. 

Aggregate Functions in SQL

These functions are used to perform various mathematical calculations on a single or group of values in the databases. The aggregate functions in SQL are very powerful to perform operations on the data. As we know about MS Excel functions, we can perform almost every mathematical calculation in Excel. This is just like MS Excel where we are also applying some formulas to the data stored in the databases. The aggregate functions return only a single value and these functions are also useful to summarize the data. When you start using these functions in SQL, you will get more familiar with the working of these functions. In aggregate functions, the NULL values are ignored while performing calculations except for the COUNT function. 

The GROUP BY and HAVING clauses of Aggregate Functions are used more often with SELECT statements in SQL queries. Let us see the syntax of aggregate functions: 

Syntax of Aggregate functions in SQL:

agg_fxn (Distinct | ALL Expressions)

• Here in the syntax, in place of agg_fxn, we can use various aggregate functions such as MIN, MAX, SUM, COUNT, AVG, etc. Now the Distinct is a modifier which is used to ignore the duplicate values. ALL modifier is used to calculate all values. If the ALL modifier is not specified inside the parentheses, it will be automatically used by default. 

1. SUM(): Sum is a function that totals the numeric values of a column and gives us the output. SUM() is a mathematical function that adds all the values in a column and returns the SUM() of that column. Suppose there is N number of employees in an organization and you want to know the total cost spent for the salaries of that employee. So, you can use the SUM function to total the salaries of employees and it will return the total salary. You can use the following query to do this:

Employee table:

The SQL query for the Employee table is:

SELECT SUM(Salary) FROM Employee;

The output for the above query will be:

2. COUNT(): The count function is very useful to get the total number of rows present in the table. You can also give a condition to count these rows and can also run it without a condition. With specific conditions, you can count only specific rows. However, it returns the number of rows of a table. This function cannot be used in MS Access. 

The syntax for this function is:

SELECT COUNT(name_of_column) FROM name_of_table;

In this syntax, the SELECT statement selects columns to count the total number of rows from the table. You need to specify the name of the table otherwise it will give an error. 

Let us see an example to better understand this function:

Here, in the above table, we can see there is a total four number of rows and there can be N number of rows in a table. So, to get the total rows of this Employee table, we will use the following query:

SELECT COUNT(Emp_ID) FROM Employee;

Output:

3. AVG(): As we already know how to calculate Average in Mathematics. So, this function does the same as we did in Maths to find the averages. We just SUM the total amount and divide it by the total number of entries to find the Average. The AVG() function do the same and gives the average of any group of integers of a column. To apply this let us see the Employee table and find the average salary of the employees:

The Query to get the average salary is:

SELECT AVG(Salary) FROM Employee;

The output of the above query will be:

4. MIN(): The MIN() function returns the minimum value from a selected column. The minimum value can be extracted when there are integer values in that column. Suppose, you want to get the minimum age of an employee from a column on the table. You can use the following query for the table Employee:

The Query will be:

SELECT MIN(Age) FROM Employee;

Output:

5. MAX(): Max function becomes very important when we want to get the maximum value of any column. But this should be kept in mind, the MAX() function will work for integer values only. As the query execution will not be placed for strings because there’s no logic to find MAX from any string. Suppose you want to get the higher salary of any employee in your organization and there is a large number of employees. So, to know what is the highest salary from the salary column of a table, you can use the following query:

The SQL query for this Employee table is:

SELECT MAX(Salary) FROM Employee;

Output:

6. FIRST(): The first function is a very useful aggregate function of SQL where we can get the first value from any selected column. Suppose there is N number of columns and you want the first value from a column. If you try manually, it will take a lot of time to check every value from each column. So, you can use the following query to get the first value from a column. 

Employee table:

The SQL query to get the first Emp_ID from the table is:

SELECT FIRST(Emp_ID) FROM Employee;

The output will be

7. LAST(): As we just discussed the FIRST function above. The LAST function is very similar to that where the difference is only that the last value of any column will be returned. This way you can get the last value from any specified column name from the table. Suppose there is N number of rows in a table and you want to see the last value of any column. So, manually it will take a lot of time to scroll the table and see the value. To make this easy, you can use the LAST() function in your SQL query that you return exactly the last value from the column.

Let us see the query for the same table:

The SQL query will be:

SELECT LAST(Emp_ID) FROM Employee;

The output of the above query will be:

Scalar Functions in SQL

These functions also return a single value from given input values. Scalar functions accept various parameters for single or multiple values and return only a single value as output. The scalar functions are very useful when we want to simplify our code. For example, if we have a complex computation that shows a number of queries and we create a scalar function that evaluates the queries by applying formula in each query. The scalar function is mostly used to perform complex calculations. These functions work on each record in the database independently which is based on the user input. As it takes single or multiple arguments, but always returns a single value. The returned single value can be of any data type. Let us see the syntax of Scalar functions to better understand this concept:

Syntax of Scalar functions:

CREATE FUNCTION [nameOfSchema]nameOfFunction (arguments or parameters)
RETURNS datatype AS
BEGIN
set_of_statements
RETURN value
END

• In the above syntax, the CREATE FUNCTION is a keyword which is used to create a new function by specifying the name. In case you don’t specify the name of the function, it will take the default name of the function as “dbo”.
• Now, the arguments that are inside the parentheses should be given to perform various operations on the data. And the RETURNS statement is used with the data type of the return value. We need to specify the datatype after we write the RETURNS command. 
• Lastly, the RETURN statement is used to return the value after performing calculations on the statements described inside the body of the function. 

1. LCASE(): LCASE stands for Lowercase which is a scalar function used to convert strings of characters to lowercase. You can apply the function to the strings of the whole column in a table and it will return the strings in lowercase characters.

Let us see the Employee table below where you can apply this function:

In the above table in column Name, some names are in Uppercase and some are in lowercase but you want all the names to be in lowercase characters. So you can use the following query to perform this operation:

SELECT LCASE(Name) FROM Employee;

Output:

2. UCASE(): UCASE stands for Uppercase and this function is used to convert all the characters of a string to uppercase. Suppose there are names in a column in the table and some of them are in lowercase characters. So you can change them to uppercase by using the following query on the Employee table:

Here in the table as you can see the names bittu and abhay are in lowercase characters and you can convert the whole column strings to uppercase by using the UCASE() function query below:

SELECT UCASE(Name) FROM Employee;

Output:

3. LEN(): This function is used to get the length of any string value. You can use this function to get the total number of characters or the length of any string. To use this function, let us see the Employee table below:

SELECT LENGTH(Name) FROM Employee;

Output:

4. MID(): This scalar function is useful when we want to extract substrings from any column containing string values. To use this function let us see the Employee table below:

The query to extract substrings from the Name column of the table is:

SELECT MID(Name, 3, 2) FROM Employee;

In the above query, there are 3 parameters passed such as the name of the column which is “Name” and the other 2 arguments are integers that specify the start of the string and the last argument is used to specify the length of the string. 

The output of the above query for the Employee table is:

5. ROUND(): Round function is used when you need to round off any numeric value which is in decimal point values. The following table Student can be used to round off the decimal values:

The query to round off the marks of these students is:

SELECT ROUND(Marks) FROM Student;

Output:

6. NOW(): This function is very useful when we want to fetch the present day’s data from any table. Suppose there is a table containing a large number of entries and you want to fetch only the present Day’s data from the table. Then you can perform this operation by using the following query on any table.

The query to fetch the present day’s data is:

SELECT Name, NOW() AS DateTime FROM Employee;

Output:

7. FORMAT(): The format () function is used to specify the format of a field. By using this function, you can simply specify the format of any field such as the name of a column, to be displayed in the table. Let us consider the Employee table that we discussed above and change the format of the Date using the Format() function.

The Query will be:

SELECT NAME, FORMAT(DATE, DD-MM-YYYY) FROM Employee;

Output:

Conclusion

The SQL functions are very useful while fetching and manipulating data from the databases. In this article, we discussed various functions used in SQL. Here we have learned mainly two types of SQL Functions which are Aggregate and Scalar functions. Although these functions have different sub-functions that we use in our queries. Hence, it provides great productivity of using these functions as it makes it easy for us to find the relevant information and change it according to our needs. Hope this article helped you to understand some basic functions of SQL that you can use whenever needed.