SQL Functions (Aggregate and Scalar Functions) (original) (raw)

Last Updated : 17 May, 2024

**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.

**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.

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:

**COUNT() Function Example

Computing total number of students.

**Query:

*SELECT COUNT() **AS NumStudents **FROM Students;

**Output:

**FIRST() Function Example

Fetching marks of first student from the Students table.

**Query:

**SELECT FIRST(MARKS) **AS MarksFirst **FROM Students;

**Output:

**LAST() **Function Example

Fetching marks of last student from the Students table.

**Query:

**SELECT LAST(MARKS) **AS MarksLast **FROM Students;

**Output:

**MAX() Function Example

Fetching maximum marks among students from the Students table.

**Query:

**SELECT MAX(MARKS) **AS MaxMarks **FROM Students;

**Output:

**MIN() Function Example

Fetching minimum marks among students from the Students table.

**Query:

**SELECT MIN(MARKS) **AS MinMarks **FROM Students;

**Output:

**SUM() Function Example

Fetching summation of total marks among students from the Students table.

**Query:

SELECT SUM(MARKS) AS TotalMarks FROM Students;

**Output:

**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:

**LCASE() Function Example

Converting names of students from the table Students to lowercase.

**Query:

**SELECT LCASE(NAME) **FROM Students;

Output:

**MID() Function Example

Fetching first four characters of names of students from the Students table.

**Query:

**SELECT MID(NAME,1,4) FROM Students;

**Output:

**LEN() Function Example

Fetching length of names of students from Students table.

**Query:

**SELECT LENGTH(NAME) **FROM Students;

Output:

**ROUND() Function Example

Fetching maximum marks among students from the Students table.

**Query:

**SELECT ROUND(MARKS,0) **FROM Students;

**Output:

**NOW() Function Example

Fetching current system time.

**Query:

SELECT NAME, NOW() AS DateTime FROM Students;

**Output:

**FORMAT() Function Example

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

**Query:

**SELECT NAME, **FORMAT(Now(),'YYYY-MM-DD') **AS Date **FROM Students;

**Output:

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.