---
description: SQL aggregate, scalar, and time functions supported by the SQL API.
title: Functions
image: https://developers.cloudflare.com/analytics/sql-api/sql-reference/functions/og.png?v=16c84c1bc000dc43
---

[Skip to content](#main-content)

> Documentation Index  
> Fetch the complete documentation index at: https://developers.cloudflare.com/analytics/llms.txt  
> Use this file to discover all available pages before exploring further.

# Functions

Last updated Oct 2, 2026|Copy as Markdown| [View as Markdown](https://developers.cloudflare.com/analytics/sql-api/sql-reference/functions/index.md)| [Agent setup](https://developers.cloudflare.com/agent-setup/)

Function names are case-insensitive.

## Aggregate functions

| Function | Description |
| --- | --- |
| `COUNT(*)` or `COUNT()` | Count matching rows or represented events. `COUNT(expression)` is not supported. |
| `SUM(expression)` | Sum a numeric expression. |
| `AVG(expression)` | Calculate the arithmetic mean of a numeric expression. |
| `MIN(expression)`, `MAX(expression)` | Return an exact extremum on an unsampled dataset. |
| `APPROX_MIN(expression)`, `APPROX_MAX(expression)` | Return the smallest or largest value observed in retained rows of an adaptively sampled dataset. |
| `countIf(condition)` | Count rows that satisfy a condition. |
| `sumIf(expression, condition)` | Sum values from rows that satisfy a condition. |
| `avgIf(expression, condition)` | Average values from rows that satisfy a condition. |
| `first_value(expression)`, `last_value(expression)` | Return the first or last value in the aggregate input. |
| `argMax(value, expression)`, `argMin(value, expression)` | Return `value` from the row with the largest or smallest `expression`. |
| `topK(expression)` | Return an array of the most frequent values. Supports ClickHouse-style parameters such as `topK(10)(expression)`. |
| `topKWeighted(expression, weight)` | Return the most frequent values using explicit weights. Supports parameters such as `topKWeighted(10)(expression, weight)`. |
| `quantileWeighted(level, expression, weight)` | Calculate a weighted quantile. `level` must be between `0` and `1`. |
| `quantileExactWeighted(level)(expression, weight)` | Alias form of `quantileWeighted`. |

`topK` accepts up to three optional parameters before its value expression. `topKWeighted` accepts the same parameters before its value and weight expressions. The requested top-k size and load factor must each be between `1` and `100`.

The compatibility aggregates from `countIf` through `quantileExactWeighted` in the table are supported by ClickHouse-backed datasets. Log Explorer-backed datasets support `COUNT`, `SUM`, `AVG`, `MIN`, `MAX`, `APPROX_MIN`, and `APPROX_MAX`.

### Adaptive sampling

For adaptively sampled event and log datasets, the SQL API automatically applies sample weights to `COUNT`, `SUM`, `AVG`, `countIf`, `sumIf`, `avgIf`, and `topK`. You do not need to use `sampleInterval` yourself for these functions. Weighted averages exclude null expression values from both the numerator and denominator.

`topKWeighted` and `quantileWeighted` use the explicit weight argument supplied by the query. Pass `sampleInterval` when you want that weight to represent the dataset's adaptive sampling.

```sql
SELECT countIf(edgeResponseStatus >= 500) AS server_errors
FROM events.httpRequests
WHERE accountTag = '<ACCOUNT_TAG>'
  AND timestamp >= NOW() - INTERVAL '1' HOUR
```

Exact `MIN` and `MAX` are rejected for adaptively sampled event and log datasets. Use `APPROX_MIN` and `APPROX_MAX` to calculate extrema from retained sample rows. These functions cannot reconstruct values from events that sampling did not retain.

`argMax` and `argMin` are also rejected on adaptively sampled datasets, except Workers Analytics Engine datasets, which preserve their existing compatibility behavior.

### DISTINCT aggregates

The following forms are supported by ClickHouse-backed unsampled datasets:

```sql
COUNT(DISTINCT expression)
SUM(DISTINCT expression)
AVG(DISTINCT expression)
```

Other `DISTINCT` aggregates and row-level `SELECT DISTINCT` are not supported. Adaptively sampled datasets reject `DISTINCT` aggregates because omitted values cannot be reconstructed. Workers Analytics Engine datasets retain `COUNT`, `SUM`, and `AVG DISTINCT` for compatibility.

Log Explorer-backed datasets support `COUNT(DISTINCT expression)` and `SUM(DISTINCT expression)`, but not `AVG(DISTINCT expression)`.

### State datasets

State dataset rows represent observations of a system's state rather than unique events. Unrestricted aggregation could produce misleading or nonsensical results, so each state dataset exposes a `valid_aggregations` list through [introspection](https://developers.cloudflare.com/analytics/sql-api/datasets/#discover-datasets). The list can contain `count`, `sum`, `avg`, `min`, or `max`.

`countIf`, `sumIf`, and `avgIf` require the corresponding base aggregation. Other compatibility aggregates, including approximate extrema, are not supported on state datasets.

## Scalar functions

The following scalar functions are supported on ClickHouse-backed datasets. Function names are case-insensitive.

| Category | Functions |
| --- | --- |
| Conditional | `if(condition, then, else)` |
| Numeric | `intDiv`, `round`, `ceil`, `floor`, `log`, `pow` |
| String | `length`, `isEmpty`, `toLower`, `toUpper`, `startsWith`, `endsWith`, `substring`, `position`, `format` |
| Conversion and representation | `toUInt8`, `toUInt32`, `bin`, `hex` |
| Bitwise | `bitAnd`, `bitCount`, `bitHammingDistance`, `bitNot`, `bitOr`, `bitRotateLeft`, `bitRotateRight`, `bitShiftLeft`, `bitShiftRight`, `bitTest`, `bitXor` |
| Date parts | `date_part`, `EXTRACT`, `toYear`, `toMonth`, `toDayOfMonth`, `toDayOfWeek`, `toHour`, `toMinute`, `toSecond`, `toYYYYMM` |
| Date and time conversion | `toUnixTimestamp`, `formatDateTime`, `toDateTime`, `toDate`, `timezone` |
| Time buckets | `toStartOfInterval`, `toStartOfDay`, `toStartOfMinute`, `toStartOfFiveMinutes`, `toStartOfTenMinutes`, `toStartOfFifteenMinutes`, `toStartOfHour`, `toStartOfWeek`, `toStartOfMonth`, `toStartOfYear` |
| Interval constructors | `toIntervalSecond`, `toIntervalMinute`, `toIntervalHour`, `toIntervalDay`, `toIntervalWeek`, `toIntervalMonth`, `toIntervalQuarter`, `toIntervalYear` |

Supported aliases include `lengthUTF8`, `empty`, `lower`, `lowerUTF8`, `upper`, `upperUTF8`, `substr`, `strpos`, `ceiling`, `ln`, `dayOfWeek`, and `toStartOfFiveMinute`.

Log Explorer-backed datasets support only a subset of these functions: `if`, `isEmpty`, `startsWith`, `endsWith`, `pow`, `length`, `toLower`, `toUpper`, `position`, one-argument `ceil` and `floor`, `log`, `toYear`, `toMonth`, `toHour`, `toMinute`, `toSecond`, `toDayOfMonth`, `toDayOfWeek`, `toYYYYMM`, `toDate`, `toStartOfWeek`, `toStartOfMonth`, and `toStartOfYear`. The API returns HTTP `422` when the selected backend does not support a function.

## Current date and time

The API resolves current-time functions once when it plans the query. Repeated uses in one query represent the same instant.

| Function | Description |
| --- | --- |
| `NOW()` | Return the current UTC timestamp. |
| `CURRENT_TIMESTAMP()` | Equivalent to `NOW()`. |
| `TODAY()` | Return the start of the current day in UTC. |
| `CURRENT_DATE()` | Equivalent to `TODAY()`. |

```sql
SELECT CURRENT_TIMESTAMP() AS queried_at, COUNT(*) AS requests
FROM events.httpRequests
WHERE accountTag = '<ACCOUNT_TAG>'
  AND timestamp >= TODAY()
```

## Time buckets and intervals

`toStartOfInterval(timestamp, interval[, timezone])` rounds a `DateTime` or `DateTime64(3)` value down to the beginning of an interval:

```sql
SELECT
  toStartOfInterval(timestamp, INTERVAL '15' MINUTE) AS bucket,
  COUNT(*) AS requests
FROM events.httpRequests
WHERE accountTag = '<ACCOUNT_TAG>'
  AND timestamp >= NOW() - INTERVAL '1' DAY
GROUP BY bucket
```

The interval must contain a positive integer and one of the following units: `YEAR`, `QUARTER`, `MONTH`, `WEEK`, `DAY`, `HOUR`, `MINUTE`, `SECOND`, `MILLISECOND`, `MICROSECOND`, or `NANOSECOND`. The optional timezone must be a string literal. `toStartOfInterval` is not supported for Log Explorer-backed datasets.

Add an interval to or subtract an interval from a timestamp expression:

```sql
NOW() - INTERVAL '15' MINUTE
NOW() - INTERVAL '7' DAY
```

The SQL API rejects scalar and aggregate functions that are not listed on this page.

Was this helpful?

YesNo

## On this page

[![](https://developers.cloudflare.com/_astro/logo.te5VL_aD.svg)Docs](https://developers.cloudflare.com/)

```json
{"@context":"https://schema.org","@type":"TechArticle","@id":"https://developers.cloudflare.com/analytics/sql-api/sql-reference/functions/#page","headline":"Functions","description":"SQL aggregate, scalar, and time functions supported by the SQL API.","url":"https://developers.cloudflare.com/analytics/sql-api/sql-reference/functions/","inLanguage":"en","image":"https://developers.cloudflare.com/analytics/sql-api/sql-reference/functions/og.png?v=16c84c1bc000dc43","dateModified":"2026-10-02","publisher":{"@type":"Organization","name":"Cloudflare","description":"One platform for your apps, agents, and workforce. Build, secure, and scale without managing infrastructure","url":"https://www.cloudflare.com/","sameAs":["https://github.com/cloudflare","https://www.linkedin.com/company/cloudflare","https://x.com/cloudflare"],"logo":{"@type":"ImageObject","url":"https://developers.cloudflare.com/logo.svg"},"address":{"@type":"PostalAddress","streetAddress":"101 Townsend St","addressLocality":"San Francisco","addressRegion":"CA","postalCode":"94107","addressCountry":"US"},"contactPoint":[{"@type":"ContactPoint","contactType":"Customer Support","url":"https://support.cloudflare.com/","availableLanguage":["English"]},{"@type":"ContactPoint","contactType":"Sales","url":"https://www.cloudflare.com/contact/","availableLanguage":["English"]}]},"isPartOf":{"@type":"WebSite","@id":"https://developers.cloudflare.com/#website","name":"Cloudflare Docs","url":"https://developers.cloudflare.com/"}}
```