SQL Functions: Aggregate and Scalar

SQL Functions: Aggregate and Scalar

3 mins read1.2K Views Comment
clickHere
Updated on Aug 8, 2022 11:59 IST

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.

2022_04_SQL-FUNCTION.jpg

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

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

About the Author

This is a collection of insightful articles from domain experts in the fields of Cloud Computing, DevOps, AWS, Data Science, Machine Learning, AI, and Natural Language Processing. The range of topics caters to upski... Read Full Bio

Comments