SQL reference·续

GROUPING SETS, ROLLUP, and CUBE

These extensions compute multiple groupings, including subtotals and grand totals, in a single query.

• GROUPING SETS: Computes exactly the groupings you list. () produces the grand total.
• ROLLUP: Computes hierarchical subtotals from left to right. ROLLUP(a, b) groups by (a, b), (a), and ().
• CUBE: Computes every combination of the listed columns. CUBE(a, b) groups by (a, b), (a), (b), and ().

-- Subtotals per department plus a grand total
SELECT department, SUM(total_amount) AS total
FROM my_namespace.sales_data
GROUP BY ROLLUP(department)

-- Every combination of department and category
SELECT department, category, SUM(total_amount) AS total
FROM my_namespace.sales_data
GROUP BY CUBE(department, category)

-- Explicit groupings
SELECT department, category, SUM(total_amount) AS total
FROM my_namespace.sales_data
GROUP BY GROUPING SETS ((department, category), (department), ())

HAVING clause

Syntax

SELECT column_list, aggregation_function(column) AS alias
FROM namespace_name.table_name
GROUP BY column_list
HAVING aggregation_function(column) comparison_operator value

Examples

SELECT department, COUNT(*) AS dept_count
FROM my_namespace.sales_data
GROUP BY department
HAVING COUNT(*) > 1000

SELECT region, SUM(total_amount) AS total
FROM my_namespace.sales_data
GROUP BY region
HAVING SUM(total_amount) > 1000000

ORDER BY clause

Syntax

ORDER BY expression [ASC | DESC] [, expression [ASC | DESC], ...]

• ASC: Ascending order (default)
• DESC: Descending order
• Multi-column ordering is supported

Examples

SELECT customer_id, total_amount
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
ORDER BY total_amount DESC
LIMIT 50

SELECT department, COUNT(*) AS dept_count
FROM my_namespace.sales_data
GROUP BY department
ORDER BY dept_count DESC, department ASC

LIMIT clause

Syntax

LIMIT number

• Type: Integer only
• Default: 500

Examples

SELECT * FROM my_namespace.sales_data LIMIT 100

OFFSET clause

OFFSET skips a specified number of rows before returning results. Use it with LIMIT to retrieve a page of results. Order by a unique column to keep the row order consistent across pages.

Syntax

LIMIT number OFFSET number

Example

This query skips the first 100 rows and returns the next 100 rows:

SELECT sale_id, total_amount
FROM my_namespace.sales_data
ORDER BY sale_id
LIMIT 100 OFFSET 100

Window functions

Window functions compute a value across a set of rows related to the current row without collapsing them into a single output row. The window is defined inline with an OVER (...) clause containing an optional PARTITION BY, ORDER BY, and frame specification.

Syntax

function(args) OVER (
    [PARTITION BY expression [, ...]]
    [ORDER BY expression [ASC | DESC] [, ...]]
    [frame_specification]
)

Supported functions

Examples

-- Rank rows within each partition
SELECT customer_id, region,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY total_amount DESC) AS rank_in_region,
       LAG(total_amount) OVER (PARTITION BY region ORDER BY total_amount DESC) AS prev_amount
FROM my_namespace.sales_data

-- Running total with an explicit frame
SELECT customer_id, total_amount,
       SUM(total_amount) OVER (ORDER BY total_amount ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS running_total
FROM my_namespace.sales_data

Note

The named WINDOW clause is not supported. Inline the OVER (...) specification at each call site instead of defining WINDOW w AS (...).

QUALIFY

QUALIFY filters rows based on the result of a window function, similar to how HAVING filters grouped rows.

-- Keep only the top 3 customers by amount in each region
SELECT customer_id, region, total_amount
FROM my_namespace.sales_data
QUALIFY ROW_NUMBER() OVER (PARTITION BY region ORDER BY total_amount DESC) <= 3

Set operations

Set operations combine the results of two or more SELECT statements.

Syntax

SELECT ... FROM table1
UNION | UNION ALL | INTERSECT | EXCEPT
SELECT ... FROM table2

Supported operations

Examples

Union

-- Find zones that had either firewall blocks OR high-risk requests
SELECT zone_id FROM my_namespace.firewall_events WHERE action = 'block'
UNION
SELECT zone_id FROM my_namespace.http_requests WHERE risk_score > 0.8

Intersect

-- Find zones with both firewall blocks AND entries in the zones table
SELECT zone_id FROM my_namespace.firewall_events WHERE action = 'block'
INTERSECT
SELECT zone_id FROM my_namespace.zones WHERE plan = 'enterprise'

Except

-- Find enterprise zones that have no firewall events
SELECT zone_id FROM my_namespace.zones WHERE plan = 'enterprise'
EXCEPT
SELECT zone_id FROM my_namespace.firewall_events

Requirements

• All queries in a set operation must return the same number of columns.
• Corresponding columns must have compatible data types.
• Column names in the result are taken from the first query.

Note

A LIMIT placed directly before a set operator is not valid. Wrap each query in a subquery if you need to limit its rows, or apply a single LIMIT to the combined result.

EXPLAIN

Returns the execution plan for a query without running it.

EXPLAIN SELECT department, COUNT(*) AS dept_count
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
GROUP BY department;

EXPLAIN FORMAT JSON

Returns the execution plan as structured JSON for programmatic analysis.

EXPLAIN FORMAT JSON SELECT * FROM my_namespace.sales_data LIMIT 10;

Expressions

Expressions can be used in SELECT, WHERE, GROUP BY, HAVING, and ORDER BY clauses.

Literals

SELECT 42 AS int_val, 3.14 AS float_val, 'hello' AS str_val, TRUE AS bool_val, NULL AS null_val
FROM my_namespace.sales_data LIMIT 1

Arithmetic operators

+, -, *, /, %

SELECT customer_id, total_amount * 1.1 AS total_with_tax, total_amount % 10 AS remainder
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
LIMIT 5

String concatenation

SELECT customer_id || ' - ' || region AS label
FROM my_namespace.sales_data
LIMIT 5

CASE expressions

Searched form:

SELECT customer_id,
    CASE
        WHEN total_amount > 1000 THEN 'high'
        WHEN total_amount > 100 THEN 'medium'
        ELSE 'low'
    END AS tier
FROM my_namespace.sales_data
LIMIT 10

Simple form:

SELECT customer_id,
    CASE region
        WHEN 'North' THEN 'N'
        WHEN 'South' THEN 'S'
        ELSE 'Other'
    END AS region_code
FROM my_namespace.sales_data
LIMIT 10

Type casting

-- CAST
SELECT CAST(total_amount AS INT) AS amount_int FROM my_namespace.sales_data LIMIT 5

-- TRY_CAST (returns NULL on failure instead of error)
SELECT TRY_CAST(customer_id AS INT) AS id_int FROM my_namespace.sales_data LIMIT 5

-- Shorthand (::)
SELECT total_amount::INT AS amount_int FROM my_namespace.sales_data LIMIT 5

EXTRACT

SELECT EXTRACT(YEAR FROM timestamp) AS yr,
       EXTRACT(MONTH FROM timestamp) AS mo,
       EXTRACT(DAY FROM timestamp) AS dy
FROM my_namespace.sales_data
LIMIT 1

Data type reference

Operator precedence

1. Comparison operators: =, !=, <, <=, >, >=, LIKE, BETWEEN, IS NULL, IS NOT NULL
2. AND (higher precedence)
3. OR (lower precedence)

Use parentheses to override default precedence:

SELECT * FROM my_namespace.sales_data WHERE (status = 404 OR status = 500) AND region = 'North'

Complete query examples

Basic query

SELECT *
FROM my_namespace.sales_data
WHERE timestamp BETWEEN '2025-09-24T01:00:00Z' AND '2025-09-25T01:00:00Z'
LIMIT 100

Filtered query with sorting

SELECT customer_id, timestamp, status, total_amount
FROM my_namespace.sales_data
WHERE status >= 400 AND total_amount > 5000
ORDER BY total_amount DESC
LIMIT 50

Aggregation with HAVING

SELECT region, COUNT(*) AS region_count, AVG(total_amount) AS avg_amount
FROM my_namespace.sales_data
WHERE status = 'completed'
GROUP BY region
HAVING COUNT(*) > 1000
ORDER BY avg_amount DESC
LIMIT 20

Conditional categorization

SELECT customer_id,
    CASE
        WHEN total_amount >= 1000 THEN 'Premium'
        WHEN total_amount >= 100 THEN 'Standard'
        ELSE 'Basic'
    END AS tier,
    total_amount
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
ORDER BY total_amount DESC
LIMIT 20

Was this helpful?

YesNo

On this page

Docs

{"@context":"https://schema.org","@type":"WebPage","@id":"https://developers.cloudflare.com/basin-sql/sql-reference/#page","headline":"SQL reference","description":"Comprehensive reference for SQL syntax, functions, and data types supported in Basin SQL.","url":"https://developers.cloudflare.com/basin-sql/sql-reference/","inLanguage":"en","image":"https://developers.cloudflare.com/basin-sql/sql-reference/og.png?v=ac761896d6b1b401","dateModified":"2026-10-21","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/"},"keywords":["SQL"]}

description: Reference for the aggregate functions supported in Basin SQL, organized by category. title: Aggregate functions image: https://developers.cloudflare.com/basin-sql/sql-reference/aggregate-functions/og.png?v=d5773fc22c1b5711

Skip to content

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