rollup

DolphinDB supports the following two standard SQL forms:

Syntax 1: Function form

SELECT/EXEC group_column(s), aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY rollup(group_column_1, group_column_2, ...);

Syntax 2: Keyword form

SELECT/EXEC group_column(s), aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY group_column_1, group_column_2, ... WITH ROLLUP;

Where:

• In Syntax 1, rollup(...) must be written in lowercase.

• group_column_1, group_column_2, ... are the columns to group by;

• In the SELECT/EXEC clause, columns other than grouping columns must be enclosed in aggregate functions (such as count, sum, avg, or max);

• ROLLUP can be used together with common SQL clauses such as WHERE, HAVING, ORDER BY, LIMIT/TOP, JOIN, and subqueries.

The two forms have identical semantics. Based on the grouped result, they generate aggregations level by level from right to left according to the grouping columns. For GROUP BY rollup(c1, c2, ..., cn), the system generates aggregate results at the following levels in sequence:

• (c1, c2, ..., cn): the finest-grained grouping result;

• (c1, c2, ..., c(n-1)): the first-level subtotal after removing the last grouping column;

• Continue rolling up level by level to the left;

• (): the total of all data.

For example:

• GROUP BY rollup(region, product) generates detail rows at the (region, product) level, subtotals at the (region) level, and a total;

• GROUP BY rollup(year, month, day) generates four levels: (year, month, day), (year, month), (year), and the total.

In subtotal or total rows generated by ROLLUP, grouping columns that have been rolled up are displayed as NULL. Therefore, NULL values in the result may indicate either null values in the original data or subtotal/grand total rows at a certain level. Interpret the result by considering the grouping levels.

The following examples create two tables: iot_alarm (IoT alarm data) and trades (financial trade data). All subsequent examples query these two tables.

// IoT: device alarm details
drop table if exists iot_alarm;
create table iot_alarm(
    site      STRING,
    devType   STRING,
    deviceId  STRING,
    alarmType STRING,
    ts        TIMESTAMP
);
go;
insert into iot_alarm values ("A","Pump" ,"P-001","HIGH_TEMP", 2026.06.27T10:00:00);
insert into iot_alarm values ("A","Pump" ,"P-001","VIBRATION", 2026.06.27T10:05:00);
insert into iot_alarm values ("A","Pump" ,"P-002","VIBRATION", 2026.06.27T10:00:00);
insert into iot_alarm values ("A","Valve","V-009","LEAK"     , 2026.06.27T10:00:00);
insert into iot_alarm values ("B","Pump" ,"P-010","HIGH_TEMP", 2026.06.27T11:00:00);
// Finance: tick-by-tick trade details
drop table if exists trades;
create table trades(
    tradeDate DATE,
    exch      STRING,
    symbol    STRING,
    qty       LONG,
    price     DOUBLE
);
go;
insert into trades values (2026.06.27, "SSE" , "600000", 100, 10.00);
insert into trades values (2026.06.27, "SSE" , "600000", 200, 10.10);
insert into trades values (2026.06.27, "SSE" , "600519",  10, 1500.00);
insert into trades values (2026.06.27, "SZSE", "000001", 300, 12.00);
insert into trades values (2026.06.27, "SZSE", "000002", 100,  8.00);

Example 1. The following query counts the number of alarms for each device and automatically generates a total row.

SELECT deviceId, count(*) as alarm_cnt
FROM iot_alarm
GROUP BY rollup(deviceId)
ORDER BY deviceId

The result can be understood as two parts:

• The number of alarms for each deviceId;

• The total row where deviceId = NULL (the first row), indicating the total number of alarms across all devices.

Example 2. The following query counts alarms in site A by device type and device, and generates device-type subtotals and a table-level total.

SELECT devType, deviceId, count(*) as alarm_cnt
FROM iot_alarm
WHERE site = "A"
GROUP BY rollup(devType, deviceId)
ORDER BY devType, deviceId

This query returns results at three levels:

• (devType, deviceId): detail aggregation by device type and device;

• (devType, NULL): device-type subtotal;

• (NULL, NULL): total.

Example 3. The following example aggregates turnover by exchange and security symbol, and automatically generates exchange subtotals and a market-wide grand total.

SELECT exch, symbol, sum(qty * price) as turnover
FROM trades
WHERE tradeDate = 2026.06.27
GROUP BY exch, symbol WITH ROLLUP
ORDER BY exch, symbol

DolphinDB supports two equivalent forms:

• GROUP BY col1, col2 WITH ROLLUP: consistent with MySQL syntax and more concise.

• GROUP BY rollup(col1, col2): consistent with PostgreSQL/SQL Server/Oracle syntax. The grouping columns are wrapped in a function, making the grouping levels easier to read.

The two forms produce the same result. Choose either form based on your preference.

Example 4. ROLLUP subtotal levels depend on the order of grouping columns. Both of the following queries are valid, but the generated subtotal levels have different meanings.

SELECT devType, deviceId, count(*) as alarm_cnt
FROM iot_alarm
WHERE site = "A"
GROUP BY rollup(devType, deviceId)
ORDER BY devType, deviceId

SELECT deviceId, devType, count(*) as alarm_cnt
FROM iot_alarm
WHERE site = "A"
GROUP BY rollup(deviceId, devType)
ORDER BY deviceId, devType

The first query generates subtotals by device type ((devType, NULL)), whereas the second generates subtotals by device ((deviceId, NULL)). Therefore, when writing SQL for reports, first determine which level to retain as the primary summary dimension, and then decide the order of the grouping columns.

Example 5. The following example first performs hierarchical aggregation by device type and device, then filters out groups with too few alarms, and finally sorts the result.

SELECT devType, deviceId, count(*) as alarm_cnt
FROM iot_alarm
WHERE site = "A"
GROUP BY devType, deviceId WITH ROLLUP
HAVING count(*) >= 2
ORDER BY devType, deviceId

This form is suitable when you need to aggregate data first and then filter for valid statistical results.

Example 6. The following example first generates hierarchical aggregation results by exchange and security symbol, then sorts the results by turnover in descending order, and uses LIMIT to return only the top 3 rows by turnover.

SELECT exch, symbol, sum(qty * price) as turnover
FROM trades
WHERE tradeDate = 2026.06.27
GROUP BY rollup(exch, symbol)
ORDER BY turnover DESC
LIMIT 3

The same query can also be written using the TOP syntax.

SELECT TOP 3 exch, symbol, sum(qty * price) as turnover
FROM trades
WHERE tradeDate = 2026.06.27
GROUP BY rollup(exch, symbol)
ORDER BY turnover DESC

Example 7. The following example joins the alarm detail table with a device dimension table to add the device region, and then generates detail rows, subtotals, and a grand total by region and device type.

drop table if exists device_info;
create table device_info(
    deviceId STRING,
    region   STRING
);
go;
insert into device_info values ("P-001", "North");
insert into device_info values ("P-002", "North");
insert into device_info values ("V-009", "North");
insert into device_info values ("P-010", "South");

SELECT d.region, a.devType, count(*) as alarm_cnt
FROM iot_alarm a
JOIN device_info d ON a.deviceId = d.deviceId
GROUP BY rollup(d.region, a.devType)
ORDER BY d.region, a.devType

Example 8. The following example first calculates the trade amount for each record in a subquery, and then performs ROLLUP aggregation by exchange and security symbol in the outer query.

SELECT exch, symbol, sum(amount) as turnover
FROM (
    SELECT exch, symbol, qty * price as amount
    FROM trades
    WHERE tradeDate = 2026.06.27
) t
GROUP BY rollup(exch, symbol)
ORDER BY exch, symbol