GROUP BY and HAVING

Make one row per group (per symbol, per broker, per account) with totals like count, sum and max, and filter the groups with HAVING.

• Category: Querying
• Keywords: group, groups, spill, ROLLUP, CUBE, GROUPING SETS, GROUPING, grand total, rollup, cube, grouping sets, subtotals, temporary files, many groups, max_groups, window with GROUP BY, GROUP BY, HAVING, aggregate, totals, subtotal, summary, report, implicit GROUP BY, group key, not grouped, count per, sum per, group by, having, implicit
• See also: Aggregate functions, SAMPLE BY, SELECT (distinct), ORDER BY, LIMIT, OFFSET, Window and series functions, Joins

GROUP BY puts the rows that have the same value (for example the same symbol) into one group, and gives one output row per group. In the select list you use aggregates like count(*), sum(x), avg(x), min(x) and max(x): they make one value from all the rows of a group. The full list is in aggregate functions.

HAVING keeps only some groups, like WHERE keeps only some rows. For groups by time (candles per minute, day, week, month), use SAMPLE BY.

Example: September 2026 summary per symbol (sample data: sample)

SELECT symbol,
       count(*)             AS days,
       min(low)             AS lowest,
       max(high)            AS highest,
       round(avg(close), 2) AS avg_close,
       sum(volume)          AS total_volume
FROM ochl_day
WHERE time IN '2026-09'
GROUP BY symbol
ORDER BY total_volume DESC;

Result:

(3 rows)

GROUP BY

Syntax:

SELECT key, ..., aggregate(...), ...
FROM ...
[WHERE condition]
GROUP BY expression, ...
[HAVING condition]
[ORDER BY ...]

Rows with equal values in all the GROUP BY expressions are one group. A key can be a column or an expression (date_trunc('month', time), upper(symbol)).

• Returns: one row per group. The order of the groups is not fixed: add ORDER BY.
• NULL: NULL key values make their own group. Aggregates skip NULL values (count(*) counts every row).
• Notes: a select alias can be used in GROUP BY: GROUP BY month groups by the column written as date_trunc('month', time) AS month. Writing the expression again does the same thing. An alias still cannot be used in HAVING.
• GROUP BY 1 means the first column of the select list, as in PostgreSQL (GROUP BY 1, 2: the first two). It cannot point at an aggregate.
• ORDER BY after GROUP BY can use the output columns (names, aliases, positions) and expressions of them (ORDER BY q / n), but not other table columns and not new aggregates.
• A window function (rank() OVER, lag(...) OVER, sum(...) OVER ()) can sit next to GROUP BY. It runs after the groups are made, over the grouped rows, as in PostgreSQL: its arguments and its ORDER BY use the group keys and aggregates, like rank() OVER (ORDER BY sum(quantity) DESC). See window functions.
• With no matching rows, GROUP BY gives 0 rows. Without GROUP BY, aggregates give one row (count(*) is 0, sum is NULL).

Example: Broker turnover as buyer on 2026-09-10 (sample data: sample)

SELECT f.buyer, b.name, count(*) AS trades, sum(f.amount) AS bought
FROM trade_log f
JOIN brokers b ON b.id = f.buyer
WHERE f.date IN '2026-09-10'
GROUP BY f.buyer, b.name
ORDER BY bought DESC;

Result:

(5 rows)

Example: Two keys: shares per broker per symbol (sample data: sample)

SELECT buyer, symbol, sum(quantity) AS shares
FROM trade_log
WHERE date IN '2026-09-10' AND buyer IN (1, 58)
GROUP BY buyer, symbol
ORDER BY buyer, symbol;

Result:

(6 rows)

Example: An expression as the key: average close per month (sample data: sample)

SELECT date_trunc('month', time) AS month, round(avg(close), 2) AS avg_close, count(*) AS days
FROM ochl_day
WHERE symbol = 'CITY'
GROUP BY date_trunc('month', time)
ORDER BY month;

Result:

(3 rows)

Example: Accounting: debit, credit and balance per account (sample data: query-app)

SELECT a.code, a.name,
       coalesce(sum(l.debit), 0)  AS debit,
       coalesce(sum(l.credit), 0) AS credit,
       coalesce(sum(l.debit), 0) - coalesce(sum(l.credit), 0) AS balance
FROM ledger l
JOIN accounts a ON a.code = l.account
GROUP BY a.code, a.name
ORDER BY a.code;

Result:

(9 rows)

Example: App users per plan, with NULL cities counted apart (sample data: query-app)

SELECT plan, count(*) AS users, count(city) AS with_city, min(created_at) AS first_signup
FROM app_users
GROUP BY plan
ORDER BY users DESC;

Result:

(3 rows)

Example: Posts per forum thread (sample data: content-forum)

SELECT t.title, count(*) AS posts, max(p.created_at) AS last_post
FROM posts p
JOIN threads t ON t.id = p.thread_id
GROUP BY t.title
ORDER BY posts DESC;

Result:

(2 rows)

Example: Sort by a calculation of the output columns: average trade size (sample data: sample)

SELECT symbol, count(*) AS n, sum(quantity) AS q
FROM trade_log
GROUP BY symbol
ORDER BY q / n DESC;

Result:

(4 rows)

Example: No rows: GROUP BY gives nothing, a plain aggregate gives one row (sample data: sample)

SELECT symbol, count(*) AS trades FROM trade_log WHERE symbol = 'ACME' GROUP BY symbol;
SELECT count(*) AS trades, sum(quantity) AS shares FROM trade_log WHERE symbol = 'ACME';

Result:

SELECT symbol, count(*) AS trades FROM trade_log WHERE symbol = 'ACME' GROUP BY symbol

(0 rows)

SELECT count(*) AS trades, sum(quantity) AS shares FROM trade_log WHERE symbol = 'ACME'

(1 row)

Example: An alias as the GROUP BY key (sample data: sample)

SELECT date_trunc('month', time) AS month, count(*) AS candles FROM ochl_day GROUP BY month ORDER BY month;

Result:

(3 rows)

Example: GROUP BY 1: the first column of the select list (sample data: sample)

SELECT symbol, sum(quantity) AS shares FROM trade_log GROUP BY 1;

Result:

(4 rows)

Example: A window function over the groups: each symbol's share of all trades (sample data: sample)

SELECT symbol, count(*) AS trades,
       round(count(*) * 100.0 / sum(count(*)) OVER (), 1) AS pct
FROM trade_log
GROUP BY symbol
ORDER BY trades DESC;

Result:

(4 rows)

Example: Rank the groups: a window function over sum() (sample data: sample)

SELECT symbol, sum(quantity) AS shares,
       rank() OVER (ORDER BY sum(quantity) DESC) AS place
FROM trade_log
GROUP BY symbol
ORDER BY place;

Result:

(4 rows)

Example: Month after month: lag() of the monthly average (sample data: sample)

SELECT date_trunc('month', time) AS month,
       round(avg(close), 2) AS avg_close,
       round(lag(avg(close)) OVER (ORDER BY date_trunc('month', time)), 2) AS previous_month
FROM ochl_day
WHERE symbol = 'CITY'
GROUP BY date_trunc('month', time)
ORDER BY month;

Result:

(3 rows)

ROLLUP, CUBE and GROUPING SETS

Syntax:

GROUP BY ROLLUP (a, b)
GROUP BY CUBE (a, b)
GROUP BY GROUPING SETS ((a, b), (a), ())
GROUP BY key, ROLLUP (a, b)
GROUPING(a [, b ...])

Subtotals and a grand total in one query, as in PostgreSQL. The query groups once for each grouping set and gives the rows of all of them together.

• Returns: the rows of each set, one set after the other. A key that is not in the row's set is NULL there. Aggregates still use the real values of all the rows of the group.
• GROUPING(a, b) tells a subtotal NULL from a NULL in the data: one bit per argument, 1 when that key is not grouped in the row's set. The leftmost argument is the highest bit, so with ROLLUP (a, b) it is 0 for the groups, 1 for the subtotals and 3 for the grand total. Its arguments must be GROUP BY keys.
• ORDER BY, LIMIT and DISTINCT are for the whole result. ORDER BY may use a result column, an aggregate or GROUPING(...).
• The grand total () always gives one row, even when no row matches.
• Notes: a key may be a column, an expression, an alias or a position (ROLLUP (1)). CUBE takes at most 12 items; a query may have at most 4096 sets.
• Not with SAMPLE BY, LATEST ON, DISTINCT ON, SELECT * or window functions (group in a subquery, and put the window outside).

Example: Shares per symbol and buyer, a subtotal per symbol, and the grand total (sample data: sample)

SELECT symbol, buyer, sum(quantity) AS shares, GROUPING(symbol, buyer) AS level
FROM trade_log
WHERE date IN '2026-09-10' AND buyer IN (1, 58)
GROUP BY ROLLUP (symbol, buyer)
ORDER BY level, symbol, buyer;

Result:

(11 rows)

Example: CUBE: totals per symbol, per buyer, and both (sample data: sample)

SELECT symbol, buyer, count(*) AS trades
FROM trade_log
WHERE date IN '2026-09-10' AND buyer IN (1, 58) AND symbol IN ('CITY', 'VOLT')
GROUP BY CUBE (symbol, buyer)
ORDER BY GROUPING(symbol, buyer), symbol, buyer;

Result:

(8 rows)

Example: GROUPING SETS: a row per month, a row per symbol, and the grand total (sample data: sample)

SELECT date_trunc('month', time) AS month, symbol, round(avg(close), 2) AS avg_close
FROM ochl_day
WHERE symbol IN ('CITY', 'VOLT')
GROUP BY GROUPING SETS ((month), (symbol), ())
ORDER BY month, symbol;

Result:

(6 rows)

Example: GROUPING() names the total rows (sample data: sample)

SELECT CASE WHEN GROUPING(symbol) = 1 THEN 'all symbols' ELSE symbol END AS symbol, sum(quantity) AS shares
FROM trade_log
WHERE date IN '2026-09-10'
GROUP BY ROLLUP (symbol)
ORDER BY GROUPING(symbol), shares DESC;

Result:

(5 rows)

Example: GROUPING() needs a GROUP BY key (sample data: sample; shows an error)

SELECT symbol, GROUPING(buyer), count(*) FROM trade_log GROUP BY ROLLUP (symbol);

Result:

Error: line 1, column 25: GROUPING(buyer): the argument must be one of the GROUP BY expressions

Big GROUP BY: many groups and memory

A GROUP BY can make millions of groups, for example one per day, symbol and broker. KDB keeps it fast and keeps the server safe.

• Notes: SAMPLE BY and a read-only database never write temporary files: they keep every group in memory, and only --max-groups stops them.
• ORDER BY ... LIMIT over hundreds of thousands of groups still turns every group into a row before the sort, so it is slower than the same query without ORDER BY.
• The limits are server settings: see protection and limits.
• To make fewer groups: add a WHERE on time, group by fewer columns, or use a longer SAMPLE BY interval.

Implicit GROUP BY

Syntax:

SELECT key, ..., aggregate(...), ... FROM ... [WHERE ...]

Like QuestDB, KDB does not need the words GROUP BY. When the select list has an aggregate and there is no GROUP BY, every select item that is not an aggregate becomes a group key.

• Returns: the same as the query with GROUP BY of those items.
• Notes: in PostgreSQL this is an error. In KDB it is allowed, but every extra column adds a key: adding close to SELECT symbol, count(*) splits the groups by close price. Writing GROUP BY makes the query clearer for readers.

Example: No GROUP BY: symbol is not an aggregate, so it is the key (sample data: sample)

SELECT symbol, max(high) AS highest FROM ochl_day WHERE time IN '2026-09';

Result:

(3 rows)

Example: The same with GROUP BY (sample data: sample)

SELECT symbol, max(high) AS highest FROM ochl_day WHERE time IN '2026-09' GROUP BY symbol;

Result:

(3 rows)

Example: Careful: one more column is one more key (sample data: sample)

SELECT symbol, round(close / 10) AS price_band, count(*) AS days
FROM ochl_day
WHERE time IN '2026-09' AND symbol = 'COAST';

Result:

(2 rows)

Example: Only aggregates: one row for the whole table (sample data: query-app)

SELECT count(*) AS lines, sum(debit) AS total_debit, sum(credit) AS total_credit FROM ledger;

Result:

(1 row)

HAVING

Syntax:

SELECT ... FROM ... [WHERE ...] GROUP BY ... HAVING condition

HAVING keeps only the groups where the condition is true. It runs after the groups are made, so it can use aggregates. WHERE runs before, on single rows.

• Returns: only the groups where the condition is true.
• NULL: a condition that is NULL removes the group.
• Notes: a select alias cannot be used in HAVING: write HAVING sum(amount) > 500000, not HAVING bought > 500000.
• HAVING needs GROUP BY or an aggregate in the query. A key column can be used too: HAVING symbol LIKE 'N%' (but WHERE is faster for that).
• Put conditions on single rows in WHERE: fewer rows are grouped, and time conditions skip partitions.

Example: Symbols with 800 shares or more on 2026-09-10 (sample data: sample)

SELECT symbol, sum(quantity) AS shares, count(*) AS trades
FROM trade_log
WHERE date IN '2026-09-10'
GROUP BY symbol
HAVING sum(quantity) >= 800
ORDER BY shares DESC;

Result:

(2 rows)

Example: Symbols that moved more than 20 dollars in September (sample data: sample)

SELECT symbol, max(high) - min(low) AS price_range
FROM ochl_day
WHERE time IN '2026-09'
GROUP BY symbol
HAVING max(high) - min(low) > 20;

Result:

(2 rows)

Example: Journal entries that do not balance (none, in a correct book) (sample data: query-app)

SELECT entry_id, sum(debit) AS debit, sum(credit) AS credit
FROM ledger
GROUP BY entry_id
HAVING sum(debit) != sum(credit);

Result:

(0 rows)

Example: Cities with two or more app users (sample data: query-app)

SELECT city, count(*) AS users
FROM app_users
WHERE city IS NOT NULL
GROUP BY city
HAVING count(*) >= 2;

Result:

(2 rows)

Example: An alias cannot be used in HAVING (sample data: sample; shows an error)

SELECT buyer, sum(amount) AS bought FROM trade_log GROUP BY buyer HAVING bought > 500000;

Result:

Error: line 1, column 74: unknown column 'bought' (table 'trade_log' has: date transaction_id symbol buyer seller quantity rate amount)

Example: HAVING without groups or aggregates (sample data: sample; shows an error)

SELECT symbol FROM trade_log HAVING symbol = 'TELCO';

Result:

Error: line 1, column 44: HAVING needs GROUP BY or aggregates

Columns that are not grouped

Syntax:

SELECT key, other_column, aggregate(...) FROM ... GROUP BY key

In PostgreSQL, a column in the select list that is neither a GROUP BY key nor inside an aggregate is an error. KDB gives no error. It shows the value of one row of the group (the first row it read), which is usually not the value you want.

• Notes: do not use this. Put the column in GROUP BY, or use an aggregate that says which value you want: first(x), last(x) (by time), min(x), max(x).
• Without GROUP BY, the same column becomes a key instead (see implicit GROUP BY).

Example: time is not grouped: KDB shows the time of one row (2025-12-28), not of the highest price (sample data: sample)

SELECT symbol, time, max(high) AS highest FROM ochl_day GROUP BY symbol;

Result:

(3 rows)

Example: Say what you want: the first and the last day of each symbol (sample data: sample)

SELECT symbol, min(time) AS first_day, max(time) AS last_day, max(high) AS highest
FROM ochl_day
GROUP BY symbol;

Result:

(3 rows)

Example: The latest close of each symbol: last(close) uses the time order (sample data: sample)

SELECT symbol, last(close) AS latest_close, first(close) AS first_close
FROM ochl_day
GROUP BY symbol;

Result:

(3 rows)