SQL Functions: Aggregate and Scalar

# SQL Functions: Aggregate and Scalar

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.

## Introduction

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

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

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.