Other functions

Given an aggregation state, this function returns the result of aggregation (or the finalized state when using a [-State](/docs/sql-reference/aggregate-functions/combinators#-state) combinator).

**Syntax**

```
finalizeAggregation(state)
```

**Arguments**

- `state` — State of aggregation. [`AggregateFunction`](/docs/sql-reference/data-types/aggregatefunction)

**Returned value**

Returns the finalized result of aggregation. [`Any`](/docs/sql-reference/data-types)

**Examples**

**Usage example**

```
SELECT finalizeAggregation(arrayReduce('maxState', [1, 2, 3]));
```

```
┌─finalizeAggregation(arrayReduce('maxState', [1, 2, 3]))─┐
│                                                       3 │
└─────────────────────────────────────────────────────────┘
```

**Combined with initializeAggregation**

```
WITH initializeAggregation('sumState', number) AS one_row_sum_state
SELECT
    number,
    finalizeAggregation(one_row_sum_state) AS one_row_sum,
    runningAccumulate(one_row_sum_state) AS cumulative_sum
FROM numbers(5);
```

```
┌─number─┬─one_row_sum─┬─cumulative_sum─┐
│      0 │           0 │              0 │
│      1 │           1 │              1 │
│      2 │           2 │              3 │
│      3 │           3 │              6 │
│      4 │           4 │             10 │
└────────┴─────────────┴────────────────┘
```

## flipCoordinates[​](#flipCoordinates "Direct link to flipCoordinates")

Introduced in: v25.10.0

Flips the x and y coordinates of geometric objects. This operation swaps latitude and longitude, which is useful for converting between different coordinate systems or correcting coordinate order.

For a Point, it swaps the x and y coordinates. For complex geometries (LineString, Polygon, MultiPolygon, Ring, MultiLineString), it recursively applies the transformation to each coordinate pair.

The function supports both individual geometry types (Point, Ring, Polygon, MultiPolygon, LineString, MultiLineString) and the Geometry variant type.

**Syntax**

```
flipCoordinates(geometry)
```

**Arguments**

- `geometry` — The geometry to transform. Supported types: Point (Tuple(Float64, Float64)), Ring (Array(Point)), Polygon (Array(Ring)), MultiPolygon (Array(Polygon)), LineString (Array(Point)), MultiLineString (Array(LineString)), or Geometry (a variant containing any of these types).

**Returned value**

The geometry with flipped coordinates. The return type matches the input type. [`Point`](/docs/sql-reference/data-types/geo#point) or [`Ring`](/docs/sql-reference/data-types/geo#ring) or [`Polygon`](/docs/sql-reference/data-types/geo#polygon) or [`MultiPolygon`](/docs/sql-reference/data-types/geo#multipolygon) or [`LineString`](/docs/sql-reference/data-types/geo#linestring) or [`MultiLineString`](/docs/sql-reference/data-types/geo#multilinestring) or [`Geometry`](/docs/sql-reference/data-types/geo)

**Examples**

**basic\_point**

```
SELECT flipCoordinates((1.0, 2.0));
```

```
(2.0, 1.0)
```

**ring**

```
SELECT flipCoordinates([(1.0, 2.0), (3.0, 4.0)]);
```

```
[(2.0, 1.0), (4.0, 3.0)]
```

**polygon**

```
SELECT flipCoordinates([[(1.0, 2.0), (3.0, 4.0)], [(5.0, 6.0), (7.0, 8.0)]]);
```

```
[[(2.0, 1.0), (4.0, 3.0)], [(6.0, 5.0), (8.0, 7.0)]]
```

**geometry\_wkt**

```
SELECT flipCoordinates(readWkt('POINT(10 20)'));
```

```
(20, 10)
```

**geometry\_polygon\_wkt**

```
SELECT flipCoordinates(readWkt('POLYGON((0 0, 5 0, 5 5, 0 5, 0 0))'));
```

```
[[(0, 0), (0, 5), (5, 5), (5, 0), (0, 0)]]
```

## formatQuery[​](#formatQuery "Direct link to formatQuery")

Introduced in: v23.10.0

Returns a formatted, possibly multi-line, version of the given SQL query. Throws in case of a parsing error.
[example:multiline]

**Syntax**

```
formatQuery(query)
```

**Arguments**

- `query` — The SQL query to be formatted. [String](/docs/sql-reference/data-types/string)

**Returned value**

The formatted query [`String`](/docs/sql-reference/data-types/string)

**Examples**

**multiline**

```
SELECT formatQuery('select a,    b FRom tab WHERE a > 3 and  b < 3');
```

```
SELECT
    a,
    b
FROM tab
WHERE (a > 3) AND (b < 3)
```

## formatQueryOrNull[​](#formatQueryOrNull "Direct link to formatQueryOrNull")

Introduced in: v23.11.0

Returns a formatted, possibly multi-line, version of the given SQL query. Returns NULL in case of a parsing error.
[example:multiline]

**Syntax**

```
formatQueryOrNull(query)
```

**Arguments**

- `query` — The SQL query to be formatted. [String](/docs/sql-reference/data-types/string)

**Returned value**

The formatted query [`String`](/docs/sql-reference/data-types/string)

**Examples**

**multiline**

```
SELECT formatQuery('select a,    b FRom tab WHERE a > 3 and  b < 3');
```

```
SELECT
    a,
    b
FROM tab
WHERE (a > 3) AND (b < 3)
```

## formatQuerySingleLine[​](#formatQuerySingleLine "Direct link to formatQuerySingleLine")

Introduced in: v23.10.0

Like formatQuery() but the returned formatted string contains no line breaks. Throws in case of a parsing error.
[example:multiline]

**Syntax**

```
formatQuerySingleLine(query)
```

**Arguments**

- `query` — The SQL query to be formatted. [String](/docs/sql-reference/data-types/string)

**Returned value**

The formatted query [`String`](/docs/sql-reference/data-types/string)

**Examples**

**multiline**

```
SELECT formatQuerySingleLine('select a,    b FRom tab WHERE a > 3 and  b < 3');
```

```
SELECT a, b FROM tab WHERE (a > 3) AND (b < 3)
```

## formatQuerySingleLineOrNull[​](#formatQuerySingleLineOrNull "Direct link to formatQuerySingleLineOrNull")

Introduced in: v23.11.0

Like formatQuery() but the returned formatted string contains no line breaks. Returns NULL in case of a parsing error.
[example:multiline]

**Syntax**

```
formatQuerySingleLineOrNull(query)
```

**Arguments**

- `query` — The SQL query to be formatted. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

The formatted query [`String`](/docs/sql-reference/data-types/string)

**Examples**

**multiline**

```
SELECT formatQuerySingleLine('select a,    b FRom tab WHERE a > 3 and  b < 3');
```

```
SELECT a, b FROM tab WHERE (a > 3) AND (b < 3)
```

## formatReadableDecimalSize[​](#formatReadableDecimalSize "Direct link to formatReadableDecimalSize")

Introduced in: v22.11.0

Given a size (number of bytes), this function returns a readable, rounded size with suffix (KB, MB, etc.) as a string.

The opposite operations of this function are [`parseReadableSize`](#parseReadableSize).

**Syntax**

```
formatReadableDecimalSize(x)
```

**Arguments**

- `x` — Size in bytes. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a readable, rounded size with suffix as a string. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Format file sizes**

```
SELECT
    arrayJoin([1, 1024, 1024*1024, 192851925]) AS filesize_bytes,
    formatReadableDecimalSize(filesize_bytes) AS filesize
```

```
┌─filesize_bytes─┬─filesize───┐
│              1 │ 1.00 B     │
│           1024 │ 1.02 KB    │
│        1048576 │ 1.05 MB    │
│      192851925 │ 192.85 MB  │
└────────────────┴────────────┘
```

## formatReadableQuantity[​](#formatReadableQuantity "Direct link to formatReadableQuantity")

Introduced in: v20.10.0

Given a number, this function returns a rounded number with suffix (thousand, million, billion, etc.) as a string.

This function accepts any numeric type as input, but internally it casts them to `Float64`.
Results might be suboptimal with large values.

**Syntax**

```
formatReadableQuantity(x)
```

**Arguments**

- `x` — A number to format. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a rounded number with suffix as a string. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Format numbers with suffixes**

```
SELECT
    arrayJoin([1024, 1234 * 1000, (4567 * 1000) * 1000, 98765432101234]) AS number,
    formatReadableQuantity(number) AS number_for_humans
```