What are SQL functions? | SQL aggregate functions and Scalar Functions

In this article, we learn SQL Functions like aggregate and Scalar Functions.
A function is a predefined formula that takes one or more arguments as input then the process arguments are returns an output. SQL has many built-in functions for performing calculations on data.

SQL Functions

There are two types of SQL functions, such as, aggregate functionsand scalar functions (non-aggregate functions) and further sub-categorised in different seven functions under each category. therefore, aggregate functions operate on many records and produce a summary, works with GROUP BY whereas scalar functions operate on each record independently.

Types of SQL functions

SQL Function	Description
SQL Aggregate Function	These SQL functions are used to do operations from the values of the column and a single value is returned. This function can produce a single value for an entire table or group. They operate on sets of rows and these return results based on groups of rows. Aggregate SQL functions return a single value, calculated from values in a column. The useful aggregate functions: SQL Count() function: Returns the numbers of rows SQL Avg() function: Returns the average value SQL First() Function: Returns the first value SQL Last() Function: Returns the last value SQL Max() function: Returns the largest value SQL Min() function: Returns the smallest value SQL Sum() function: Returns the sum
SQL Scalar functions	SQL scalar functions return a single value, based on the input value. There are useful scalar functions: SQL UCASE() Function: Converts a field to upper case SQL CASE() Function: Converts a field to lower case SQL MID() Function: Extract characters from a text field SQL LEN() Function: Returns the length of a text field SQL ROUND() Function: Rounds a numeric field to the number of decimals specified SQL NOW() Function: Returns the current system date and time SQL FORMAT() Function: Formats how a field is to be displayed

SQL Function

Description

SQL Aggregate Function

These SQL functions are used to do operations from the values of the column and a single value is returned. This function can produce a single value for an entire table or group. They operate on sets of rows and these return results based on groups of rows. Aggregate SQL functions return a single value, calculated from values in a column.
The useful aggregate functions:
SQL Count() function: Returns the numbers of rows
SQL Avg() function: Returns the average value
SQL First() Function: Returns the first value
SQL Last() Function: Returns the last value
SQL Max() function: Returns the largest value
SQL Min() function: Returns the smallest value
SQL Sum() function: Returns the sum

SQL Scalar functions

SQL scalar functions return a single value, based on the input value.
There are useful scalar functions:
SQL UCASE() Function: Converts a field to upper case
SQL CASE() Function: Converts a field to lower case
SQL MID() Function: Extract characters from a text field
SQL LEN() Function: Returns the length of a text field
SQL ROUND() Function: Rounds a numeric field to the number of decimals specified
SQL NOW() Function: Returns the current system date and time
SQL FORMAT() Function: Formats how a field is to be displayed

Demo Table:

EmployeeID	Emp_Name	Father_Name	City	Salary
21	Arihant Thakur	Raja Ram Thakur	Nagpur	25000
22	Gyan Dubey	Ratan Lala Dubey	Mumbai	35000
23	Ankit Agrahari	Santosh Agrahari	Sultanpur	30000
24	Prakash Shukla	Kuldeep Shukla	Meerut	28000
25	Gyanendra Singh	Rajesh Singh	Jhansi	32000
26	Nitin Singh	Jay Prakash Singh	Agra	36000
27	Anil Kumar Yadav	Suraj Yadav	Lucknow	38000
28	Kapil Gupta	Om Kumar Gupta	Barabanki	40000
29	Narayan Thappa	Amit Kumar Thappa	Allahabad	20000
30	Anand Pratap Singh	Shiv Pratap Singh	Delhi	40000
31	Jyoti Thakur	Pradeep Thakur	Noida	40000
32	Rajni Chaturvedi	Kamal Chaturvedi	Surajpur	45000
33	Niharika Khan	Danish Khan	Amritsar	20000
34	Payal Rastogi	Ajay Rastogi	Jalandhar	26000
35	Anand Tiwari	Suresh Tiwari	Patna	38000

SQL Aggregate Functions

COUNT(): These are used to count the number of rows returned in a SELECT statement. therefore, it can’t be used in MS ACCESS.

Syntax: SELECT COUNT(column_name) FROM table_name;

Example:

1. Computing the total number of students

SELECT COUNT(*) AS Emp_Name FROM Employee;

1	SELECT COUNT(*) AS Emp_Name FROM Employee;

Count SQL Functions
2. Computing number of Employee with unique/distinct salary

SELECT COUNT(DISTINCT Salary) AS Emp_Name FROM Employee;

1	SELECT COUNT(DISTINCT Salary) AS Emp_Name FROM Employee;

COUNT DISTINCT functions in sql

AVG(): Therefore, it always returns average value after calculating from the values in a numeric column.

Syntax: SELECT AVG(column_name) FROM table_name;

Example:

1. Computing average salary of employee.

SELECT AVG(Salary) AS AvgSalary FROM Employee;

1	SELECT AVG(Salary) AS AvgSalary FROM Employee;

AVG SQL Functions

FIRST(): The FIRST() function always returns the first value of the selected column.

Syntax: SELECT FIRST(column_name) FROM table_name;

Example:

1. Fetching salary of first Employee from the Employee table

SELECT FIRST(Salary) AS SalaryFirst FROM Employee;

1	SELECT FIRST(Salary) AS SalaryFirst FROM Employee;

SQL FIRST Function
2. Fetching City of first Employee from the Employee table

SELECT FIRST(City) AS CityFirst FROM Employee;

1	SELECT FIRST(City) AS CityFirst FROM Employee;

SQL FIRST Function Output

LAST(): The LAST() function always returns the last value of the selected column. therefore, It can be used only in MS ACCESS.

Syntax:SELECT LAST(column_name) FROM table_name;

Example:

1. Fetching Salary of last Employee from the Employee table.

SELECT LAST(Salary) AS SalaryLast FROM Employee;

1	SELECT LAST(Salary) AS SalaryLast FROM Employee;

SQL LAST Function
2. Fetching City of last Employee from the Employee table

SELECT LAST(City) AS CityLast FROM Employee;

1	SELECT LAST(City) AS CityLast FROM Employee;

SQL LAST FUnction Example

MAX(): The MAX() function always returns the maximum (MAX) value of the selected column.

Syntax:SELECT MAX(column_name) FROM table_name;

Example:

1. Fetching maximum Salary among Employee from the Employee table.

SELECT MAX(Salary) AS MaxMarks FROM Employee;

1	SELECT MAX(Salary) AS MaxMarks FROM Employee;

MAX example ouput

MIN(): The MIN() function always returns the minimum (MIN) value of the selected column.

Syntax:SELECT MIN(column_name) FROM table_name;

Example:

1. Fetching minimum Salary among Employee from the Employee table

SELECT MIN(Salary) AS MinMarks FROM Employee;

1	SELECT MIN(Salary) AS MinMarks FROM Employee;

MIN Function in SQL

SUM(): The SUM() function always returns the sum (Total) of all the values of the selected column

Syntax:SELECT SUM(column_name) FROM table_name;

Example:

1. Fetching summation of total Salary among Employee from the Employee table

SELECT SUM(Salary) AS TotalSalary FROM Employee;

1	SELECT SUM(Salary) AS TotalSalary FROM Employee;

SUM Function in SQL

SQL Scalar Functions

UCASE(): Therefore, It will convert the value of a field to uppercase.

Syntax:SELECT UCASE(column_name) FROM table_name;

Example:

1. Converting names of Employees from the table Employee to uppercase

SELECT UCASE(Emp_Name) FROM Employee;

1	SELECT UCASE(Emp_Name) FROM Employee;

UCASE example ouput

LCASE(): Therefore, It will convert the value of a field to lowercase.

Syntax:SELECT LCASE(column_name) FROM table_name;

Example:

1. Converting names of Employees from the table Employee to lowercase

SELECT UCASE(Emp_Name) FROM Employee;

1	SELECT UCASE(Emp_Name) FROM Employee;

LCASE example ouput

MID(): Therefore, the MID() function extracts texts from the text field.

Syntax:SELECT MID(column_name,start,length) AS some_name FROM table_name;

Therefore, here, the specifying length is optional, and start signifies start position ( starting from 1 )

Example:

1. Fetching first Eleven characters of names of employees from the Employee table

SELECT MID(Emp_Name,1,11) FROM Employee;

1	SELECT MID(Emp_Name,1,11) FROM Employee;

MID example ouput

LEN(): Therefore, The LEN() function always returns the length of the value in a text field

Syntax:SELECT LENGTH(column_name) FROM table_name;

Example:

1. To Fetching the length of names of employees from Employee table

SELECT LENGTH(Emp_Name) FROM Employee;

1	SELECT LENGTH(Emp_Name) FROM Employee;

LEN example ouput

ROUND(): The ROUND() function, it is used for the purpose to round a numeric field to the number of decimals (.) specified. Important NOTE: furthermore, Many database systems have adopted the IEEE 754 standard for arithmetic operations, that says, when any numeric .5 is rounded it results to the nearest even integer i.e, 4.5 and 5.5 both gets rounded off to 5.

Syntax:SELECT ROUND(column_name,decimals) FROM table_name;

Example:

1. Fetching maximum Salary among employees from the Employee table.

SELECT ROUND(Salary,0) FROM Employee;

1	SELECT ROUND(Salary,0) FROM Employee;

ROUND example ouput

NOW(): Therefore, the NOW() function always returns the current system date and time.

Syntax:SELECT NOW() FROM table_name;

Example:

1. To Fetch the current system time.

SELECT Emp_Name, NOW() AS DateTime FROM Employee;

1	SELECT Emp_Name, NOW() AS DateTime FROM Employee;

Output: The output will comes as per asking question.

FORMAT(): Therefore, the FORMAT() function is used to format, furthermore how a field is to be displayed.

Syntax:SELECT FORMAT(column_name,format) FROM table_name;

Example:

1. The Formatting current date as ‘YYYY-MM-DD’.

SELECT Emp_Name, FORMAT(Now(),'YYYY-MM-DD') AS Date FROM Employee;

1	SELECT Emp_Name, FORMAT(Now(),'YYYY-MM-DD') AS Date FROM Employee;

Output: The output will comes as per asking question.

Useful Functions in SQL List

Therefore Functions in SQL have many built-in functions for performing processing on numeric data or string.
furthermore, there are the following is the list of all useful SQL built-in functions:-

S.N.	Functions in SQL	Description
1.	SQL COUNT Function	The SQL COUNT() aggregate function, is used for the purpose to count the number of rows in a database table.
2.	SQL MAX Function	The SQL MAX() aggregate function allows us to select the maximum (highest) value for a certain column.
3.	SQL MIN Function	The SQL MIN() aggregate function allows us to select the minimum (lowest) value for a certain column.
4.	SQL SUM Function	The SQL SUM() aggregate function used for the purpose that it allows selecting the total for a numeric column.
5.	SQL AVG Function	The SQL AVG() aggregate function selects the average value for a certain table column.
6.	SQL COUNT Function	The SQL COUNT() aggregate function, is used for the purpose to count the number of rows in a database table.
7.	SQL CONCAT Function	Therefore, it is used to concatenate any string inside any SQL command.
8.	SQL Numeric Functions	Therefor, The complete list of SQL functions required to manipulate The numbers in SQL.
9.	SQL String Functions	Therefore, The complete list of SQL functions required to manipulate the strings in SQL.
10.	SQL SQRT Functions	Therefore, it is used to generate a square root of a given number.
11.	SQL RAND Function	Therefore, it is used to generate a random number by using the SQL command.

What are SQL functions? | SQL aggregate functions and Scalar Functions

SQL Functions

Types of SQL functions

Demo Table:

SQL Aggregate Functions

Example:

Example:

Example:

Example:

Example:

Example:

Example:

SQL Scalar Functions

Example:

Example:

Example:

Example:

Example:

Example:

Example:

Useful Functions in SQL List

Related Posts

About Author

admin