SQL Functions (Aggregate and Scalar Functions)
SQL Functions are built-in programs that are used to perform different
operations on the database.
There are two types of functions in SQL:
● Aggregate Functions
● Scalar Functions
SQL Aggregate Functions
SQL Aggregate Functions operate on a data group and return a singular output.
They are mostly used with the GROUP BY clause to summarize data.
Some common Aggregate functions with Syntax and description are shown in
the table below.
Aggregate
Function Description Syntax
Calculates the average SELECT AVG(column_name)
AVG() value FROM table_name;
Counts the number of SELECT COUNT(column_name)
COUNT() rows FROM table_name
Returns the first value in SELECT FIRST(column_name)
FIRST() an ordered set of values FROM table_name;
Returns the last value in SELECT LAST(column_name)
LAST() an ordered set of values FROM table_name;
Retrieves the maximum SELECT MAX(column_name)
MAX() value from a column FROM table_name;
Aggregate
Function Description Syntax
Retrieves the minimum SELECT MIN(column_name)
MIN() value from a column FROM table_name;
Calculates the total sum of
SELECT SUM(column_name)
values in a numeric
FROM table_name;
SUM() column
SQL Scalar functions
SQL Scalar Functions are built-in functions that operate on a single value and
return a single value.
Scalar functions in SQL helps in efficient data manipulation and simplification of
complex calculations in SQL queries.
Scalar
function Description Syntax
Converts a string to SELECT UCASE(column_name)
UCASE() uppercase FROM table_name;
Converts a string to SELECT LCASE(column_name) FROM
LCASE() lowercase table_name;
Extracts a substring from SELECT MID(column_name, start,
MID() a string length) FROM table_name;
Returns the length of a SELECT LEN(column_name) FROM
LEN() string table_name;
Scalar
function Description Syntax
Rounds a number to a
SELECT ROUND(column_name,
specified number of
decimals) FROM table_name;
ROUND() decimals
Returns the current date
SELECT NOW();
NOW() and time
Formats a value with the SELECT FORMAT(column_name,
FORMAT() specified format format) FROM table_name;
SQL Functions Examples
Let’s look at some examples of SQL Functions. We will cover examples of SQL
aggregate functions and scalar functions.
We will perform queries on the given SQL table:
Aggregate Functions Examples
Let’s look at the examples of each aggregate function in SQL.
AVG() Function Example
Computing average marks of students.
Query:
SELECT AVG(MARKS) AS AvgMarks FROM Students;
Output:
AvgMarks
80
COUNT() Function Example
Computing total number of students.
Query:
SELECT COUNT(*) AS NumStudents FROM Students;
Output:
NumStudents
FIRST() Function Example
Fetching marks of first student from the Students table.
Query:
SELECT FIRST(MARKS) AS MarksFirst FROM Students;
Output:
MarksFirst
90
LAST() Function Example
Fetching marks of last student from the Students table.
Query:
SELECT LAST(MARKS) AS MarksLast FROM Students;
Output:
MarksLas
t
85
MAX() Function Example
Fetching maximum marks among students from the Students table.
Query:
SELECT MAX(MARKS) AS MaxMarks FROM Students;
Output:
MaxMark
s
95
MIN() Function Example
Fetching minimum marks among students from the Students table.
Query:
SELECT MIN(MARKS) AS MinMarks FROM Students;
Output:
MinMark
s
50
SUM() Function Example
Fetching summation of total marks among students from the Students table.
Query:
SELECT SUM(MARKS) AS TotalMarks FROM Students;
Output:
TotalMark
s
400
Scalar Functions Examples
Let’s look at some examples of each Scalar Function in SQL.
UCASE() Function Example
Converting names of students from the table Students to uppercase.
Query:
SELECT UCASE(NAME) FROM Students;
Output:
NAME
HARSH
SURESH
PRATIK
DHANRA
J
NAME
RAM
LCASE() Function Example
Converting names of students from the table Students to lowercase.
Query:
SELECT LCASE(NAME) FROM Students;
Output:
NAME
harsh
suresh
pratik
dhanraj
ram
MID() Function Example
Fetching first four characters of names of students from the Students table.
Query:
SELECT MID(NAME,1,4) FROM Students;
Output:
NAME
HARS
SURE
PRAT
DHAN
RAM
LEN() Function Example
Fetching length of names of students from Students table.
Query:
SELECT LENGTH(NAME) FROM Students;
Output:
NAME
7
NAME
ROUND() Function Example
Fetching maximum marks among students from the Students table.
Query:
SELECT ROUND(MARKS,0) FROM Students;
Output:
MARKS
90
50
80
95
85
NOW() Function Example
Fetching current system time.
Query:
SELECT NAME, NOW() AS DateTime FROM Students;
Output:
NAME DateTime
HARSH 1/13/2017 1:30:11 PM
SURESH 1/13/2017 1:30:11 PM
PRATIK 1/13/2017 1:30:11 PM
DHANRA
1/13/2017 1:30:11 PM
J
RAM 1/13/2017 1:30:11 PM
FORMAT() Function Example
Formatting current date as ‘YYYY-MM-DD’.
Query:
SELECT NAME, FORMAT(Now(),'YYYY-MM-DD') AS Date FROM Students;
Output:
NAME Date
2017-01-1
HARSH
3
2017-01-1
SURESH
3
2017-01-1
PRATIK
3
NAME Date
DHANRA 2017-01-1
J 3
2017-01-1
RAM
3
Important Points About SQL Functions
● SQL functions are built-in programs that are used to manipulate data in
various ways.
● There are different types of SQL functions – Aggregate functions and
Scalar functions.
● Aggregate functions perform calculations on a group of values and
return a single value. Example SUM, AVG, COUNT.
● Scalar functions operate on a single value and return a single value.
Example UPPER, LOWER, SUBSTRING.
● SQL functions can be used in different SQL statements, such as SELECT,
WHERE, GROUP BY, and ORDER BY, to improve data processing and
analysis.
