---
title: "Subqueries & Aggregates"
description: "Using subqueries and aggregate functions"
canonical_url: "https://docs.ctrl-neo.dev/en/tauri-sqlite-orm/advanced-topics/subqueries-and-aggregates"
---
# Subqueries & Aggregates

> Using subqueries and aggregate functions

This guide covers advanced query patterns using subqueries and aggregate functions.

## Subqueries

Subqueries allow you to use the result of one query within another query. They're powerful for complex filtering and comparisons.

### Creating Subqueries

```ts
import { subquery, scalarSubquery } from '@type32/tauri-sqlite-orm';
import { eq, inArray, exists } from '@type32/tauri-sqlite-orm';

const users = sqliteTable('users', {
  id: integer('id').primaryKey().autoincrement(),
  name: text('name').notNull(),
  departmentId: integer('department_id').notNull(),
});

const departments = sqliteTable('departments', {
  id: integer('id').primaryKey().autoincrement(),
  name: text('name').notNull(),
});
```

### Subquery in IN Clause

```ts
// Find all users in the 'Engineering' department
const engineeringQuery = db.select(departments)
  .where(eq(departments._.columns.name, 'Engineering'));

const engineeringUsers = await db.select(users)
  .where(inArray(users._.columns.departmentId, subquery(engineeringQuery)))
  .all();

// Generated SQL (simplified):
// SELECT * FROM users 
// WHERE department_id IN (SELECT id FROM departments WHERE name = 'Engineering')
```

### Subquery with NOT IN

```ts
import { notIn } from '@type32/tauri-sqlite-orm';

// Find users NOT in the 'HR' department
const hrQuery = db.select(departments)
  .where(eq(departments._.columns.name, 'HR'));

const nonHrUsers = await db.select(users)
  .where(notIn(users._.columns.departmentId, subquery(hrQuery)))
  .all();
```

### EXISTS Subqueries

```ts
const posts = sqliteTable('posts', {
  id: integer('id').primaryKey().autoincrement(),
  title: text('title').notNull(),
  authorId: integer('author_id').notNull(),
});

// Find users who have written at least one post
const usersWithPosts = await db.select(users)
  .where(
    exists(subquery(
      db.select(posts)
        .where(eq(posts._.columns.authorId, users._.columns.id))
    ))
  )
  .all();

// Find users who haven't written any posts
const usersWithoutPosts = await db.select(users)
  .where(
    notExists(subquery(
      db.select(posts)
        .where(eq(posts._.columns.authorId, users._.columns.id))
    ))
  )
  .all();
```

### Scalar Subquery Comparisons

```ts
import { gtSubquery, ltSubquery, eqSubquery } from '@type32/tauri-sqlite-orm';

const orders = sqliteTable('orders', {
  id: integer('id').primaryKey().autoincrement(),
  userId: integer('user_id').notNull(),
  total: integer('total').notNull(),
});

// Find orders greater than the average
const avgQuery = scalarSubquery(
  db.db.select('SELECT AVG(total) FROM orders')
);

const aboveAverageOrders = await db.select(orders)
  .where(gtSubquery(orders._.columns.total, avgQuery))
  .all();
```

## Aggregate Functions

Aggregate functions perform calculations on sets of rows. The ORM provides type-safe aggregate functions.

### Available Aggregates

```ts
import { 
  count, 
  countDistinct, 
  sum, 
  avg, 
  min, 
  max, 
  groupConcat 
} from '@type32/tauri-sqlite-orm';
```

### Using Aggregates

While the query builder doesn't yet have a dedicated aggregate API, you can use aggregates with raw SQL:

```ts
// Count all users
const result = await db.db.select('SELECT COUNT(*) as count FROM users');
console.log(result[0].count);

// Or use the .count() helper
const count = await db.select(users).count();
```

### GROUP BY with Aggregates

```ts
const posts = sqliteTable('posts', {
  id: integer('id').primaryKey().autoincrement(),
  title: text('title').notNull(),
  authorId: integer('author_id').notNull(),
  views: integer('views').notNull().default(0),
});

// Count posts per author
const postsByAuthor = await db.db.select(`
  SELECT 
    author_id,
    COUNT(*) as post_count,
    SUM(views) as total_views,
    AVG(views) as avg_views
  FROM posts
  GROUP BY author_id
`);
```

### SQLite GROUP_CONCAT

```ts
// Concatenate related values
const tagsByPost = await db.db.select(`
  SELECT 
    post_id,
    GROUP_CONCAT(tag_name, ', ') as tags
  FROM post_tags
  GROUP BY post_id
`);

// Result: [{ post_id: 1, tags: 'javascript, typescript, node' }, ...]
```

## Complex Example: Subquery + Aggregates

```ts
const products = sqliteTable('products', {
  id: integer('id').primaryKey().autoincrement(),
  name: text('name').notNull(),
  categoryId: integer('category_id').notNull(),
  price: integer('price').notNull(),
});

const categories = sqliteTable('categories', {
  id: integer('id').primaryKey().autoincrement(),
  name: text('name').notNull(),
});

// Find categories with above-average priced products
const avgPriceQuery = db.db.select('SELECT AVG(price) FROM products');

const expensiveCategories = await db.select(categories)
  .where(
    exists(subquery(
      db.select(products)
        .where(eq(products._.columns.categoryId, categories._.columns.id))
        .where(gtSubquery(
          products._.columns.price,
          scalarSubquery(avgPriceQuery)
        ))
    ))
  )
  .all();
```

## Best Practices

1. **Use .count() helper** instead of raw COUNT(*) when possible
2. **Index subquery columns** for better performance
3. **Test subquery performance** - sometimes JOINs are faster
4. **Use .toSQL() to verify** what SQL is generated
5. **Consider using relations** instead of manual subqueries when possible

## Performance Tips

```ts
// ❌ Slow: Correlated subquery for each row
const usersWithPostCount = await db.db.select(`
  SELECT u.*, 
    (SELECT COUNT(*) FROM posts WHERE author_id = u.id) as post_count
  FROM users u
`);

// ✅ Better: Use a JOIN or relation with .count()
const usersWithPosts = await db.select(users)
  .include({ posts: true })
  .all();

// Then count in JavaScript:
const usersWithCounts = usersWithPosts.map(user => ({
  ...user,
  postCount: user.posts.length
}));
```

## Debugging Subqueries

```ts
// Build subquery first
const subq = db.select(departments)
  .where(eq(departments._.columns.name, 'Engineering'));

console.log('Subquery:', subq.toSQL());

// Then use in main query
const mainQuery = db.select(users)
  .where(inArray(users._.columns.departmentId, subquery(subq)));

console.log('Main query:', mainQuery.toSQL());

// Execute
const results = await mainQuery.all();
```

Subqueries and aggregates give you powerful tools for complex data analysis while maintaining type safety!


## Sitemap

See the full [sitemap](https://docs.ctrl-neo.dev/sitemap.md) for all pages.