Group By

The groupBy method allows you to group your query results by specified columns, enabling aggregation operations.

Basic Usage

import { createQueryBuilder } from '@hypequery/clickhouse';

const db = createQueryBuilder<Schema>();

// Group by a single column
const query = db
  .table('orders')
  .select(['user_id', 'COUNT(*)'])
  .groupBy(['user_id'])
  .toSQL();
// Result: SELECT user_id, COUNT(*) FROM orders GROUP BY user_id

Type Definitions

groupBy(columns: (keyof T | TableColumn<Schema> | GroupByExpression)[]): this

Parameters

columns: Array of columns or expressions to group by
- keyof T: Column names from the table type
- TableColumn<Schema>: Cross-table column references
- GroupByExpression: Complex grouping expressions

Returns

Returns the query builder instance for method chaining.

Examples

Single Column Grouping

// Group by user_id
const query = db
  .table('orders')
  .select(['user_id', 'COUNT(*)'])
  .groupBy(['user_id'])
  .toSQL();
// Result: SELECT user_id, COUNT(*) FROM orders GROUP BY user_id

Grouping with Aggregations

import { rawAs } from '@hypequery/clickhouse';

// Multiple aggregations
const query = db
  .table('orders')
  .select([
    'user_id',
    rawAs('COUNT(*)', 'order_count'),
    rawAs('SUM(total)', 'total_spent')
  ])
  .groupBy(['user_id'])
  .toSQL();
// Result: SELECT user_id, COUNT(*) AS order_count, SUM(total) AS total_spent FROM orders GROUP BY user_id

Common Aggregation Functions

Function	Description	Example
`COUNT(*)`	Count all rows	`COUNT(*)`
`COUNT(column)`	Count non-null values	`COUNT(user_id)`
`COUNT(DISTINCT column)`	Count unique values	`COUNT(DISTINCT user_id)`
`SUM(column)`	Sum of values	`SUM(total)`
`AVG(column)`	Average of values	`AVG(total)`
`MAX(column)`	Maximum value	`MAX(total)`
`MIN(column)`	Minimum value	`MIN(total)`
`STDDEV(column)`	Standard deviation	`STDDEV(total)`
`VAR(column)`	Variance	`VAR(total)`

Type Safety

The groupBy method provides full TypeScript support:

interface OrderSchema {
  orders: {
    id: 'UInt32';
    user_id: 'UInt32';
    total: 'Float64';
    status: 'String';
    created_at: 'DateTime';
  };
}

const db = createQueryBuilder<OrderSchema>();

// ✅ Type-safe column references
const query = db
  .table('orders')
  .select(['user_id', 'COUNT(*)'])
  .groupBy(['user_id']) // TypeScript knows 'user_id' is a valid column
  .toSQL();

// ❌ TypeScript error for invalid column
const query2 = db
  .table('orders')
  .select(['user_id', 'COUNT(*)'])
  .groupBy(['invalid_column']) // TypeScript error
  .toSQL();