---
title: "ZCQL Functions"
url: https://docs.catalyst.zoho.com/en/cloud-scale/help/zcql/zcql-functions/
description: "ZCQL is Catalyst's own query language modelled after familiar query languages that you can use to query your app's database efficiently."
last_updated: 2024-09-03T11:01:06Z
service: Cloud Scale
type: general
---

# ZCQL Functions

ZCQL supports some mathematical and analytical built-in functions that help you perform quick arithmetic operations on the data and manipulate the result set of a search query to meet your requirements. These functions are used in the `SELECT` statement for the columns that are generated in the output.

### MIN()

 The `MIN()` function returns the minimum value of the selected column. You can use it to return the smallest value of a column that contains numerical data or the record that comes first in the alphabetical order.
 The basic syntax for using a `MIN()` function along with the `SELECT` statement is:

```sql
SELECT MIN(column_name) FROM base_table_name 
```

**Example**:

To view the least expensive movie from the _Pricing_ table, execute the following query:

```sql
SELECT MIN(Price) FROM Pricing 
```

This will generate the following output:

<table class="content-table">
<thead>
<tr>
<th><strong>MIN(Price)</strong></th>
</tr>
</thead>
<tbody>
<tr>
<td>7.44</td>
</tr>
</tbody>
</table>

### MAX()

The `MAX()` function returns the maximum value of the selected column. You can use it to return the largest value of a column that contains numerical data or the record that comes last in the alphabetical order.
 The basic syntax for using a `MAX()` function with the `SELECT` statement is:

```sql
SELECT MAX(column_name) FROM base_table_name 
```

**Example**:

To view the most expensive movie from the _Pricing_ table, execute the following query:

```sql
SELECT MAX(Price) FROM Pricing 
```

This will generate the following output:

<table class="content-table">
<thead>
<tr>
<th><strong>MAX(Price)</strong></th>
</tr>
</thead>
<tbody>
<tr>
<td>11.50</td>
</tr>
</tbody>
</table>

### COUNT() 

The `COUNT()` function returns the value for the number of rows in the records that are returned for a particular column in the output. The `COUNT` function is also used with the `SELECT` statement.
The basic syntax for using a `COUNT()` function with the `SELECT` statement is:

```sql
SELECT COUNT(column_name) FROM base_table_name
```

**Example:**

To view the number of theaters in New York City and Albany, from the _Theaters_ table, execute the following query:

```sql
SELECT COUNT(TheaterName) FROM Theaters
WHERE Location='New York City' OR Location='Albany'
```

This will generate the following output:

<table class="content-table">
<thead>
<tr>
<th><strong>COUNT(TheaterName)</strong></th>
</tr>
</thead>
<tbody>
<tr>
<td>2</td>
</tr>
</tbody>
</table>

### SUM()

The `SUM()` function calculates and returns the total sum of a numeric column's records. This can only be used for a column that contains numerical data. The `SUM()` function can also be used with the `SELECT` statement for a singular column.

The basic syntax for using a `SUM()` function with the `SELECT` statement is:

```sql
SELECT SUM(column_name) FROM base_table_name
```

**Example**:

To view the sum total of the ticket charges for 'The First Purge' and 'Hotel Transylvania 3: Summer Vacation' from the _Pricing_ table, execute the following query:

```sql
SELECT SUM(Price) FROM Pricing
WHERE MovieID='2056' OR MovieID='2058'
```

This will generate the following output:

<table class="content-table">
<thead>
<tr>
<th><strong>SUM(Price)</strong></th>
</tr>
</thead>
<tbody>
<tr>
<td>20.7</td>
</tr>
</tbody>
</table>

### AVG()

The `AVG()` function calculates and returns the average value for a numeric column's records. This can only be used for a column that contains numerical data. The `AVG()` function is used along with the `SELECT` statement for a particular column alone.

The `AVG ()` ZCQL function can be applied on the following data types:
- **Date**
- **DateTime**
- **Boolean**

> **Note:** 

* This functionality is only available in **ZCQL V2**.

* From **December 01st, 2024**, all your current projects in all your [Orgs](https://docs.catalyst.zoho.com/en/getting-started/catalyst-organizations/) present in the [Development Environment](https://docs.catalyst.zoho.com/en/deployment-and-billing/environments/development-environment/) will be automatically mapped to **ZCQL V2 Parser**.

* From **April 01st, 2025**, all the projects present in all *Orgs* that have already been mapped to **ZCQL V2 Parser** in *Development Environment* will be automatically mapped to **ZCQL V2 Parser** in the [Production Environment](https://docs.catalyst.zoho.com/en/deployment-and-billing/environments/production-environment/), if and when production is enabled for the project.

The basic syntax for using a `AVG()` function with the `SELECT` statement is:

```sql
SELECT AVG(column_name) FROM base_table_name
```

**Example**:

To view the average ticket price for the movies in the _Pricing_ table, execute the following query:

```sql
SELECT AVG(Price) As AverageTicketPrice FROM Pricing 
```

This will generate the following output:

<table class="content-table">
<thead>
<tr>
<th><strong>AVG(Price)</strong></th>
</tr>
</thead>
<tbody>
<tr>
<td>9.19</td>
</tr>
</tbody>
</table>

### DISTINCT

The `DISTINCT` statement returns only the distinct record values for a column. If there are duplicate values in a column, this statement will not return the duplicates. The `DISTINCT` statement is used along with the `SELECT` statement in a query.

The basic syntax for using a `SELECT DISTINCT` statement is:

```sql
SELECT DISTINCT column_name(s) FROM base_table_name
```

**Example**:

To view the distinct theaters the movies are being screened at, execute the following query:

```sql
SELECT DISTINCT Theaters.TheaterName FROM Movies
INNER JOIN Theaters ON Movies.TheaterID=Theaters.TheaterID
```

This will generate the following output:

<table class="content-table">
<thead>
<tr>
<th><strong>TheaterName</strong></th>
</tr>
</thead>
<tbody>
<tr>
<td>The Express Cinemas</td>
</tr>
<tr>
<td>Cosmos Theater</td>
</tr>
<tr>
<td>FunTime Cinemas</td>
</tr>
</tbody>
</table>

### Use Multiple ZCQL Functions

You can use multiple ZCQL functions on the same column in a single ZCQL query statement.

**Example Database**:

The employee details of Zylker Technologies are being maintained in the [Data Store](https://docs.catalyst.zoho.com/en/cloud-scale/help/data-store/introduction/) in the *Zylker_Employee_DB* table. The table contains the following columns and rows:

<table class="content-table">
	<thead>
		<tr>
			<th class="w15p">ID</th>
			<th class="w30p">Name</th>
			<th class="w30p">Department</th>
            <th class="w25p">Salary</th>
		</tr>
	</thead>
	<tbody>
		<tr>
			<td>ZT-001</td>
			<td>Amelia Burrows</td>
			<td>Product Management</td>
            <td>15000</td>
		</tr>
        <tr>
			<td>ZT-2001</td>
			<td>Bruce Wayne</td>
			<td>Sr. Management</td>
            <td>85000</td>
		</tr>
        <tr>
			<td>ZT-239</td>
			<td>Clark Kane</td>
			<td>Media Relations</td>
            <td>4500</td>
		</tr>
        <tr>
			<td>ZT-4289</td>
			<td>Michelle Mascarenhas</td>
			<td>Finance</td>
            <td>89000</td>
		</tr>
	</tbody>
</table>

Next, we apply multiple ZCQL functions together in the query, as shown below:

```sql
SELECT MIN (Salary), MAX (Salary), COUNT (Salary), SUM (Salary), AVG (Salary) FROM Zylker_Employee_DB
```

<table class="content-table">
	<thead>
		<tr>
			<th class="w20p">MAX(Salary)</th>
			<th class="w20p">SUM(Salary)</th>
			<th class="w20p">MIN(Salary)</th>
            <th class="w20p">AVG(Salary)</th>
            <th class="w20p">COUNT(Salary)</th>
		</tr>
	</thead>
	<tbody>
		<tr>
			<td>89000</td>
			<td>193500</td>
			<td>4500</td>
            <td>48975</td>
            <td>4</td>
		</tr>
	</tbody>
</table>

> **Note:** 

* This functionality is only available in **ZCQL V2**.

* From **December 01st, 2024**, all your current projects in all your [Orgs](https://docs.catalyst.zoho.com/en/getting-started/catalyst-organizations/) present in the [Development Environment](https://docs.catalyst.zoho.com/en/deployment-and-billing/environments/development-environment/) will be automatically mapped to **ZCQL V2 Parser**.

* From **April 01st, 2025**, all the projects present in all *Orgs* that have already been mapped to **ZCQL V2 Parser** in *Development Environment* will be automatically mapped to **ZCQL V2 Parser** in the [Production Environment](https://docs.catalyst.zoho.com/en/deployment-and-billing/environments/production-environment/), if and when production is enabled for the project.