GROUP BY keyword

Groups aggregation calculations by one or several keys. In QuestDB, this clause is optional.

Syntax

SELECT column [, column ...], aggregation [, aggregation ...]
FROM tableName
[GROUP BY column [, column ...]];

note

QuestDB groups aggregation results implicitly and does not require the GROUP BY keyword. It is only supported for convenience. Using the GROUP BY clause explicitly will return the same results as if the clause was omitted.

Examples

The below queries perform aggregations on a single key. Using GROUP BY explicitly or implicitly yields the same results:

Single key aggregation, explicit GROUP BYDemo this query

SELECT symbol, avg(price), count()
FROM fx_trades
WHERE timestamp IN '$today'
GROUP BY symbol
LIMIT 5;

symbol	avg	count
EURCAD	1.5956	31597
EURUSD	1.1701	315311
USDMXN	17.243	31852
GBPJPY	212.12	31442
AUDJPY	113.10	32153

Single key aggregation, implicit GROUP BYDemo this query

SELECT symbol, avg(price), count()
FROM fx_trades
WHERE timestamp IN '$today'
LIMIT 5;

The below queries perform aggregations on multiple keys. Using GROUP BY explicitly or implicitly yields the same results:

Multiple key aggregation, explicit GROUP BYDemo this query

SELECT symbol, side, avg(price), count()
FROM fx_trades
WHERE timestamp IN '$today'
GROUP BY symbol, side
LIMIT 5;

symbol	side	avg	count
USDCAD	sell	1.3601	79061
NZDUSD	buy	0.5944	31364
EURUSD	sell	1.1697	157235
EURCHF	buy	0.9149	15206
EURUSD	buy	1.1705	158076

Multiple key aggregation, implicit GROUP BYDemo this query

SELECT symbol, side, avg(price), count()
FROM fx_trades
WHERE timestamp IN '$today'
LIMIT 5;

When used explicitly, every non-aggregated column in the SELECT clause must appear in GROUP BY, otherwise an error will be returned:

Error - side is missing in the GROUP BY clause

SELECT symbol, side, avg(price)
FROM fx_trades
GROUP BY symbol;

The reverse is allowed. Extra columns in GROUP BY that are not in SELECT will produce a valid result, but the same symbol may appear multiple times (once per value of the extra key). This can be misleading, which is a good reason to prefer implicit GROUP BY where QuestDB always groups by exactly the non-aggregated columns in SELECT:

Extra GROUP BY key not in SELECTDemo this query

SELECT symbol, avg(price)
FROM fx_trades
WHERE timestamp IN '$today'
GROUP BY symbol, side
ORDER BY symbol
LIMIT 6;

symbol	avg
AUDCAD	0.9846
AUDCAD	0.9838
AUDJPY	113.1422
AUDJPY	113.0659
AUDNZD	1.2127
AUDNZD	1.2118

See also

• PIVOT - Transform GROUP BY results from rows to columns
• SAMPLE BY - Time-series aggregation
• Aggregation functions - Available aggregate functions