SQL Functions: Aggregate and Scalar

In this article, we will discuss about different SQL Functions: Aggregate and Scalar. Aggregate contains function like min, max, sum count while scalar contains function like now, round, mid etc.

Introduction

In this article, we will discuss SQL Functions: Aggregate and Scalar.

In our ongoing series on Structured Query Language, you must have understood what SQL is and how it is used to handle databases.

Table of Content

• What are SQL functions?
• Aggregate Functions
• Scalar Functions

In this article, I am going to consider the following table to explain to you the operators in SQL.

Patients Table

Patient ID	Patient Name	Sex	Age	Address	Postal Code	State	Country	Hemoglobin
01	Sheela	F	23	Flat no 201, Vasavi Heights, Yakutapura	500023	Telangana	India	82.321
02	Rehan	M	21	Building no 2, Yelahanka	560063	Karnataka	India	83.231
03	Anay	M	56	H No 1, Panipat	132140	Haryana	India	94.567
04	Mahira	F	42	House no 12, Gandhinagar	382421	Gujarat	India	78.567
05	Nishant	M	12	Sunflower Heights, Thane	400080	Maharashtra	India	65.234

What are SQL functions?

SQL functions are used to perform operations such as mathematical calculations, string concatenation, etc. They are categorized into Aggregate Functions and Scalar Functions.

To know more about, Database and SQL Online Courses and Certifications, click here.

Let us start by understanding the aggregate functions.

Aggregate Functions

Aggregate functions are used to perform mathematical calculations on data. They usually return a single value as output.

The most common aggregate functions are:

COUNT()

Used to return the number of records in a table based on the mentioned conditions.

Syntax:

[code]
SELECT COUNT(Column_Name)FROM TABLE_NAME
WHERE Condition;
[/code]
 

Example:

[code]
SELECT COUNT(PatientID) FROM Patients;
[/code
 

SUM()

Used to return an arithmetic addition of numeric values of a column in a table.

Syntax:

[code]
SELECT SUM(Column_Name) FROM TABLE_NAME;
[/code]
 

Example:

[code]
SELECT SUM(Age)FROM Patients;
[/code]
 

AVG()

Used to return an average of numeric values of a column in a table.

Syntax:

[code]
SELECT AVG(Column_Name) FROM TABLE_NAME;
[/code]
 

Example:

[code]
SELECT AVG(Age)FROM Patients;
[/code]
 

MIN()

Used to return the least numeric values from a column in a table.

Syntax:

[code]
SELECT MIN(Column_Name) FROM TABLE_NAME;
[/code]
 

Example:

[code]
SELECT MIN(Age)FROM Patients;
[/code]
 

MAX()

Used to return the greatest numeric values from a column in a table.

Syntax:

[code]
SELECT MAX(Column_Name) FROM TABLE_NAME;
[/code]
 

Example:

[code]
SELECT MAX(Age)FROM Patients;
[/code]
 

FIRST()

Used to return the first value from a column in a table.

Syntax:

[code]
SELECT FIRST(Column_Name) FROM TABLE_NAME;
[/code]
 

Example:

[code]
SELECT FIRST(PatientName)FROM Patients;
[/code]
 

LAST()

Used to return the last value from a column in a table.

Syntax:

[code]
SELECT LAST(Column_Name) FROM TABLE_NAME;
[/code]
 

Example:

[code]
SELECT LAST(PatientName)FROM Patients;
[/code]
 

Now, we will understand the Scalar functions.

Scalar Functions

Scalar functions are also used to return a single value after performing data operation.

The most common Scalar Functions are:

FORMAT()

Used to define a format of the fields.

Syntax:

[code]
SELECT FORMAT(INPUT, Format);
[/code]
 

Example:

[code]
SELECT FORMAT(ABCDEF, “##-##-##”);
[/code]
 

LCASE()

Used to convert values of a string column to lowercase.

Syntax:

[code]
SELECT LCASE(Column_Name) FROM TABLE_NAME;
[/code]
 

Example:

[code]
SELECT LCASE(PatientName) FROM Patients;
[/code]
 

UCASE()

Used to convert values of a string column to uppercase.

Syntax:

[code]
SELECT UCASE(Column_Name) FROM TABLE_NAME;
[/code]
 

Example:

[code]
SELECT UCASE(PatientName) FROM Patients;
[/code]
 

LEN()

Used to retrieve the length of the specified value or length of values in a column.

Syntax:

[code]
SELECT LEN(Column_Name) AS LenCol FROM TABLE_NAME;
[/code]
 

Example:

[code]
SELECT UCASE(PatientName) AS PatientLen FROM Patients;
[/code]
 

MID()

Used to retrieve substring from the string type of values.

Syntax:

[code]
SELECT MID(Column_Name, START, LENGTH)
FROM TABLE_NAME;
[/code]
 

Example:

[code]
SELECT MID(PatientName, 3, 4)
FROM Patients;
[/code]
 

ROUND()

Used to round off a numeric value.

Syntax:

[code]
SELECT ROUND(Column_Name, Decimals)
FROM TABLE_NAME;
[/code]
 

Example:

[code]
SELECT ROUND(Hemoglobin, 2)
FROM Patients;
[/code]
 

NOW()

Used to return the current date and time. By default, the current date and time are retrieved in the format “YY-MM-DD HH:MM:SS”

Syntax:

[code]
SELECT NOW();
[/code]
 

Example:

[code]
SELECT NOW();
[/code]
 

Conclusion

With this, we end this article on SQL Functions. We hope you found it informative. You can refer to the article on MySQL Commands for a detailed walkthrough of all commands.

Top Trending Articles:

Data Analyst Interview Questions | Data Science Interview Questions | Machine Learning Applications | Big Data vs Machine Learning | Data Scientist vs Data Analyst | How to Become a Data Analyst | Data Science vs. Big Data vs. Data Analytics | What is Data Science | What is a Data Scientist | What is Data Analyst