# QBAggregate

## Overview

The `QBAggregate` class represents aggregate expressions, such as `count`, `min`, and `max`, within a `QBSelect` query. It provides a range of methods and properties for constructing and manipulating aggregate expressions, allowing for customization of query results and conditions.

Aggregates can be used to calculate summaries, apply mathematical functions, or manipulate data within a query. Properties like `count`, `sum`, and `avg` allow direct creation of aggregate expressions, while others like `abs` or `round` perform mathematical operations. Sorting and conditional operations can also be applied using properties such as `asc`, `desc`, and `isNull`.

Methods extend the functionality of aggregates by enabling operations like comparison (`eq`, `between`, `like`), mathematical manipulation (`plus`, `minus`, `mod`), and substring extraction (`substring`). These features provide fine-grained control over how aggregate data is computed and retrieved.

The `QBAggregate` class integrates with query builder constructs like [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn), [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition), and [QBSort](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbsort), enabling complex query logic and data transformations.

## Properties Summarized

| Type                                                                                                 | Name                       | Summary                                                                      |
| ---------------------------------------------------------------------------------------------------- | -------------------------- | ---------------------------------------------------------------------------- |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)           | [abs](#abs)                | Create abs(column) expression                                                |
| [QBSort](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbsort)               | [asc](#asc)                | Create an ascending sort expression                                          |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)           | [avg](#avg)                | Create an aggregate average expression.                                      |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)           | [bit\_length](#bit_length) | Create bit\_length(column) expression                                        |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)           | [ceil](#ceil)              | Create ceil(column) expression                                               |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)           | [count](#count)            | Create an aggregate count expression.                                        |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)           | [day](#day)                | Extract day from date                                                        |
| [QBSort](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbsort)               | [desc](#desc)              | Create an descending sort expression                                         |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)           | [distinct](#distinct)      | Add a distinct qualifier to the aggregate                                    |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)           | [floor](#floor)            | Create floor(column) expression                                              |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)           | [hour](#hour)              | Extract hour from date                                                       |
| [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition)     | [isNull](#isnull)          | Compare column with null.                                                    |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)           | [len](#len)                | Create length(column) expression                                             |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)           | [lower](#lower)            | Create lower(column) expression                                              |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)           | [max](#max)                | Create an aggregate max expression.                                          |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)           | [min](#min)                | Create an aggregate min expression.                                          |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)           | [minute](#minute)          | Extract minute from date                                                     |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)           | [month](#month)            | Extract month from date                                                      |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)           | [not](#not)                | Create a negated condition.                                                  |
| [QBTableClause](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbtableclause) | [parent](#parent)          | Get query builder parent table clause, this may be a query or a join clause. |
| [QBSelect](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbselect)           | [root](#root)              | Get query builder parent.                                                    |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)           | [round](#round)            | Create round(column) expression                                              |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)           | [second](#second)          | Extract second from date                                                     |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)           | [sqrt](#sqrt)              | Create sqrt(column) expression                                               |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)           | [sum](#sum)                | Create an aggregate sum expression.                                          |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)           | [trim](#trim)              | Create trim(column) expression                                               |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)           | [upper](#upper)            | Create upper(column) expression                                              |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)           | [year](#year)              | Extract year from date                                                       |

## Methods Summarized

| Type                                                                                             | Name                                              | Summary                                                                                |
| ------------------------------------------------------------------------------------------------ | ------------------------------------------------- | -------------------------------------------------------------------------------------- |
| [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition) | [between(value1, value2)](#between-value1-value2) | Compare column to a range of 2 values or other columns.                                |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)       | [cast(type)](#cast-type)                          | Create cast(column, type) expression                                                   |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)       | [concat(arg)](#concat-arg)                        | Concatename with value                                                                 |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)       | [divide(arg)](#divide-arg)                        | Divide by value                                                                        |
| [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition) | [eq(value)](#eq-value)                            | Compare column with a value or another column.                                         |
| [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition) | [ge(value)](#ge-value)                            | Compare column with a value or another column.                                         |
| [Number](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/number)                     | [getFlags()](#getflags)                           | The flags are a bit pattern consisting of 1 or more of the following bits: - JSColumn. |
| [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string)                     | [getTypeAsString()](#gettypeasstring)             | Column type as a string                                                                |
| [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition) | [gt(value)](#gt-value)                            | Compare column with a value or another column.                                         |
| [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition) | [isin(query)](#isin-query)                        | Compare column with subquery result.                                                   |
| [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition) | [isin(values)](#isin-values)                      | Compare column with values.                                                            |
| [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition) | [isin(customQuery, args)](#isin-customquery-args) | Compare column with custom query result.                                               |
| [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition) | [le(value)](#le-value)                            | Compare column with a value or another column.                                         |
| [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition) | [like(pattern)](#like-pattern)                    | Compare column with a value or another column.                                         |
| [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition) | [like(pattern, escape)](#like-pattern-escape)     | Compare column with a value or another column.                                         |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)       | [locate(arg)](#locate-arg)                        | Create locate(arg) expression                                                          |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)       | [locate(arg, start)](#locate-arg-start)           | Create locate(arg, start) expression                                                   |
| [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition) | [lt(value)](#lt-value)                            | Compare column with a value or another column.                                         |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)       | [minus(arg)](#minus-arg)                          | Subtract value                                                                         |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)       | [mod(arg)](#mod-arg)                              | Create mod(arg) expression                                                             |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)       | [multiply(arg)](#multiply-arg)                    | Multiply with value                                                                    |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)       | [nullif(arg)](#nullif-arg)                        | Create nullif(arg) expression                                                          |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)       | [plus(arg)](#plus-arg)                            | Add up value                                                                           |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)       | [substring(pos)](#substring-pos)                  | Create substring(pos) expression                                                       |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)       | [substring(pos, len)](#substring-pos-len)         | Create substring(pos, len) expression                                                  |

## Properties Detailed

### abs

Create abs(column) expression

**Type**\
[QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) a QBFunction representing the absolute value function.

**Sample**

```js
query.result.add(query.columns.custname.abs)
```

### asc

Create an ascending sort expression

**Type**\
[QBSort](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbsort) a QBSort representing an ascending sort order.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
query.sort
.add(query.joins.orders_to_order_details.columns.quantity.asc)
.add(query.columns.companyid)
foundset.loadRecords(query)
```

### avg

Create an aggregate average expression.

**Type**\
[QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) a QBAggregate representing the average aggregate function.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
	query.groupBy.addPk() // have to group by on pk when using having-conditions in (foundset) pk queries
	.root.having.add(query.joins.orders_to_order_details.columns.quantity.avg.eq(1))
	foundset.loadRecords(query)
```

### bit\_length

Create bit\_length(column) expression

**Type**\
[QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) a QBFunction representing the bit length function.

**Sample**

```js
query.result.add(query.columns.custname.bit_length)
```

### ceil

Create ceil(column) expression

**Type**\
[QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) a QBFunction representing the ceil function.

**Sample**

```js
query.result.add(query.columns.mycol.ceil)
```

### count

Create an aggregate count expression.

**Type**\
[QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) a QBAggregate representing the count aggregate function.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
	query.groupBy.addPk() // have to group by on pk when using having-conditions in (foundset) pk queries
	.root.having.add(query.joins.orders_to_order_details.columns.quantity.count.eq(0))
	foundset.loadRecords(query)
```

### day

Extract day from date

**Type**\
[QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) a QBFunction representing the extraction of the day from a date.

**Sample**

```js
query.result.add(query.columns.mydatecol.day)
```

### desc

Create an descending sort expression

**Type**\
[QBSort](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbsort) a QBSort representing a descending sort order.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
query.sort
.add(query.joins.orders_to_order_details.columns.quantity.desc)
.add(query.columns.companyid)
foundset.loadRecords(query)
```

### distinct

Add a distinct qualifier to the aggregate

**Type**\
[QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)

**Sample**

```js
// count the number of countries that we ship orders to
var query = datasources.db.example_data.orders.createSelect();
query.result.add(query.columns.shipcountry.count.distinct);
```

### floor

Create floor(column) expression

**Type**\
[QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) a QBFunction representing the floor function.

**Sample**

```js
query.result.add(query.columns.mycol.floor)
```

### hour

Extract hour from date

**Type**\
[QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) a QBFunction representing the extraction of the hour from a date.

**Sample**

```js
query.result.add(query.columns.mydatecol.hour)
```

### isNull

Compare column with null.

**Type**\
[QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition) a QBCondition representing the "is null" comparison.

**Sample**

```js
query.where.add(query.columns.flag.isNull)
```

### len

Create length(column) expression

**Type**\
[QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) a QBFunction representing the length function.

**Sample**

```js
query.result.add(query.columns.custname.len)
```

### lower

Create lower(column) expression

**Type**\
[QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) a QBFunction representing the lower case transformation.

**Sample**

```js
query.result.add(query.columns.custname.lower)
```

### max

Create an aggregate max expression.

**Type**\
[QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) a QBAggregate representing the maximum aggregate function.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
	query.groupBy.addPk() // have to group by on pk when using having-conditions in (foundset) pk queries
	.root.having.add(query.joins.orders_to_order_details.columns.quantity.count.max(10))
	foundset.loadRecords(query)
```

### min

Create an aggregate min expression.

**Type**\
[QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) a QBAggregate representing the minimum aggregate function.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
	query.groupBy.addPk() // have to group by on pk when using having-conditions in (foundset) pk queries
	.root.having.add(query.joins.orders_to_order_details.columns.quantity.count.min(10))
	foundset.loadRecords(query)
```

### minute

Extract minute from date

**Type**\
[QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) a QBFunction representing the extraction of the minute from a date.

**Sample**

```js
query.result.add(query.columns.mydatecol.minute)
```

### month

Extract month from date

**Type**\
[QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) a QBFunction representing the extraction of the month from a date.

**Sample**

```js
query.result.add(query.columns.mydatecol.month)
```

### not

Create a negated condition.

**Type**\
[QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) a QBColumn representing the negated condition.

**Sample**

```js
query.where.add(query.columns.flag.not.eq(1))
```

### parent

Get query builder parent table clause, this may be a query or a join clause.

**Type**\
[QBTableClause](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbtableclause)

**Sample**

```js
var query = datasources.db.example_data.person.createSelect();
	query.where.add(query.joins.person_to_parent.joins.person_to_parent.columns.name.eq('john'))
	foundset.loadRecords(query)
```

### root

Get query builder parent.

**Type**\
[QBSelect](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbselect)

**Sample**

```js
var subquery = datasources.db.example_data.order_details.createSelect();

	var query = datasources.db.example_data.orders.createSelect();
	query.where.add(query
		.or
			.add(query.columns.order_id.not.isin([1, 2, 3]))

			.add(query.exists(
					subquery.where.add(subquery.columns.orderid.eq(query.columns.order_id)).root
			))
		)

	foundset.loadRecords(query)
```

### round

Create round(column) expression

**Type**\
[QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) a QBFunction representing the round function.

**Sample**

```js
query.result.add(query.columns.mycol.round)
```

### second

Extract second from date

**Type**\
[QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) a QBFunction representing the extraction of the second from a date.

**Sample**

```js
query.result.add(query.columns.mydatecol.second)
```

### sqrt

Create sqrt(column) expression

**Type**\
[QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) a QBFunction representing the square root function.

**Sample**

```js
query.result.add(query.columns.custname.sqrt)
```

### sum

Create an aggregate sum expression.

**Type**\
[QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) a QBAggregate representing the sum aggregate function.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
	query.groupBy.addPk() // have to group by on pk when using having-conditions in (foundset) pk queries
	.root.having.add(query.joins.orders_to_order_details.columns.quantity.count.sum(10))
	foundset.loadRecords(query)
```

### trim

Create trim(column) expression

**Type**\
[QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) a QBFunction representing the trim function.

**Sample**

```js
query.result.add(query.columns.custname.trim)
```

### upper

Create upper(column) expression

**Type**\
[QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) a QBFunction representing the upper case transformation.

**Sample**

```js
query.result.add(query.columns.custname.upper)
```

### year

Extract year from date

**Type**\
[QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) a QBFunction representing the extraction of the year from a date.

**Sample**

```js
query.result.add(query.columns.mydatecol.year)
```

## Methods Detailed

### between(value1, value2)

Compare column to a range of 2 values or other columns.

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **value1** ;
* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **value2** ;

**Returns:** [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition) a QBCondition representing the "between" comparison for the two values.

**Sample**

```js
query.where.add(query.columns.flag.between(0, 5))
```

### cast(type)

Create cast(column, type) expression

**Parameters**

* [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **type** string type, see QUERY\_COLUMN\_TYPES

**Returns:** [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) a QBFunction representing the cast function with the specified type.

**Sample**

```js
query.result.add(query.columns.mycol.cast(QUERY_COLUMN_TYPES.TYPE_INTEGER))
```

### concat(arg)

Concatename with value

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg** valeu to concatenate with

**Returns:** [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) a QBFunction representing the concatenation operation.

**Sample**

```js
query.result.add(query.columns.firstname.concat(' ').concat(query.columns.lastname))
```

### divide(arg)

Divide by value

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg** nr to divide by

**Returns:** [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) a QBFunction representing the division operation.

**Sample**

```js
query.result.add(query.columns.mycol.divide(2))
```

### eq(value)

Compare column with a value or another column.\
Operator: equals

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **value** ;

**Returns:** [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition) a QBCondition representing the "equals" comparison.

**Sample**

```js
query.where.add(query.columns.flag.eq(1))
```

### ge(value)

Compare column with a value or another column.\
Operator: greaterThanOrEqual

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **value** ;

**Returns:** [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition) a QBCondition representing the "greater than or equal to" comparison.

**Sample**

```js
query.where.add(query.columns.flag.ge(2))
```

### getFlags()

The flags are a bit pattern consisting of 1 or more of the following bits:\
\- JSColumn.UUID\_COLUMN\
\- JSColumn.EXCLUDED\_COLUMN\
\- JSColumn.TENANT\_COLUMN

**Returns:** [Number](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/number) an integer representing the flags of the column.

### getTypeAsString()

Column type as a string

**Returns:** [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) a string representing the column type.

### gt(value)

Compare column with a value or another column.\
Operator: greaterThan

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **value** ;

**Returns:** [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition) a QBCondition representing the "greater than" comparison.

**Sample**

```js
query.where.add(query.columns.flag.gt(0))
```

### isin(query)

Compare column with subquery result.

**Parameters**

* [QBPart](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbpart) **query** subquery

**Returns:** [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition) a QBCondition representing the "in" comparison with a subquery.

**Sample**

```js
query.where.add(query.columns.flag.isin(query2))
```

### isin(values)

Compare column with values.

**Parameters**

* [Array](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/array) **values** array of values

**Returns:** [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition) a QBCondition representing the "in" comparison with a list of values.

**Sample**

```js
query.where.add(query.columns.flag.isin([1, 5, 99]))
```

### isin(customQuery, args)

Compare column with custom query result.

**Parameters**

* [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **customQuery** custom query
* [Array](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/array) **args** query arguments

**Returns:** [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition) a QBCondition representing the "in" comparison with a custom query and arguments.

**Sample**

```js
query.where.add(query.columns.ccy.isin("select ccycode from currencies c where c.category = " + query.getTableAlias() + ".currency_category and c.flag = ?", ['T']))
```

### le(value)

Compare column with a value or another column.\
Operator: lessThanOrEqual

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **value** ;

**Returns:** [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition) a QBCondition representing the "less than or equal to" comparison.

**Sample**

```js
query.where.add(query.columns.flag.le(2))
```

### like(pattern)

Compare column with a value or another column.\
Operator: like

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **pattern** the string value of the pattern

**Returns:** [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition) a QBCondition representing the "like" comparison with a pattern.

**Sample**

```js
query.where.add(query.columns.companyname.like('Serv%'))

// case-insensitive compares can be done using the upper (or lower) functions,
// this can be useful when using for example German letters like ß,
query.where.add(query.columns.companyname.upper.like(query.functions.upper('groß%')))
```

### like(pattern, escape)

Compare column with a value or another column.\
Operator: like, with escape character

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **pattern** the string value of the pattern
* [Number](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/number) **escape** the escape char

**Returns:** [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition) a QBCondition representing the "like" comparison with a pattern and an escape character.

**Sample**

```js
query.where.add(query.columns.companyname.like('X_%', '_'))
```

### locate(arg)

Create locate(arg) expression

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg** string to locate

**Returns:** [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) a QBFunction representing the locate function for the specified string.

**Sample**

```js
query.result.add(query.columns.mycol.locate('sample'))
```

### locate(arg, start)

Create locate(arg, start) expression

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg** string to locate
* [Number](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/number) **start** start pos

**Returns:** [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) a QBFunction representing the locate function starting from the specified position.

**Sample**

```js
query.result.add(query.columns.mycol.locate('sample', 5))
```

### lt(value)

Compare column with a value or another column.\
Operator: lessThan

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **value** ;

**Returns:** [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition) a QBCondition representing the "less than" comparison.

**Sample**

```js
query.where.add(query.columns.flag.lt(99))
```

### minus(arg)

Subtract value

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg** nr to subtract

**Returns:** [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) a QBFunction representing the subtraction operation.

**Sample**

```js
query.result.add(query.columns.mycol.minus(2))
```

### mod(arg)

Create mod(arg) expression

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg** mod arg

**Returns:** [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) a QBFunction representing the modulo operation.

**Sample**

```js
query.result.add(query.columns.mycol.mod(2))
```

### multiply(arg)

Multiply with value

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg** nr to multiply with

**Returns:** [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) a QBFunction representing the multiplication operation.

**Sample**

```js
query.result.add(query.columns.mycol.multiply(2))
```

### nullif(arg)

Create nullif(arg) expression

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg** object to compare

**Returns:** [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) a QBFunction representing the nullif function.

**Sample**

```js
query.result.add(query.columns.mycol.nullif('none'))
```

### plus(arg)

Add up value

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg** nr to add

**Returns:** [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) a QBFunction representing the addition operation.

**Sample**

```js
query.result.add(query.columns.mycol.plus(2))
```

### substring(pos)

Create substring(pos) expression

**Parameters**

* [Number](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/number) **pos** ;

**Returns:** [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) a QBFunction representing the substring function starting from the specified position.

**Sample**

```js
query.result.add(query.columns.mycol.substring(3))
```

### substring(pos, len)

Create substring(pos, len) expression

**Parameters**

* [Number](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/number) **pos** ;
* [Number](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/number) **len** ;

**Returns:** [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) a QBFunction representing the substring function with the specified position and length.

**Sample**

```js
query.result.add(query.columns.mycol.substring(3, 2))
```

***