Aggregation

-- Total of all order amounts
SELECT SUM(amount) AS total FROM orders;

-- Total per user
SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id;

from django.db.models import Sum

# Total of all order amounts
total = Order.objects.aggregate(total=Sum("amount"))["total"]

# Total per user
per_user = Order.objects.values("user_id").annotate(total=Sum("amount"))

from sqlalchemy import select, func

with Session(engine) as session:
    # Total of all order amounts
    total = session.scalar(
        select(func.sum(Order.amount))
    )

    # Total per user
    rows = session.execute(
        select(Order.user_id, func.sum(Order.amount).label("total"))
        .group_by(Order.user_id)
    ).all()

from sqlalchemy import func
from sqlmodel import select

with Session(engine) as session:
    # Total of all order amounts
    total = session.exec(
        select(func.sum(Order.amount))
    ).one()

    # Total per user
    per_user = session.exec(
        select(Order.user_id, func.sum(Order.amount).label("total"))
        .group_by(Order.user_id)
    ).all()

from tortoise.functions import Sum

# Total of all order amounts
total = await Order.annotate(
    total=Sum("amount")
).values("total")

# Total per user
per_user = await Order.annotate(
    total=Sum("amount")
).group_by("user_id").values("user_id", "total")

from peewee import fn

# Total of all order amounts
total = Order.select(fn.SUM(Order.amount)).scalar()

# Total per user
per_user = (
    Order.select(
        Order.user,
        fn.SUM(Order.amount).alias("total"),
    )
    .group_by(Order.user)
)

// Total of all order amounts
const result = await prisma.order.aggregate({
  _sum: { amount: true },
});
const total = result._sum.amount;

// Total per user
const perUser = await prisma.order.groupBy({
  by: ["userId"],
  _sum: { amount: true },
});

// Total of all order amounts
const result = await db.select({
  total: sum(orders.amount),
}).from(orders);

// Total per user
const perUser = await db.select({
  userId: orders.userId,
  total: sum(orders.amount),
})
  .from(orders)
  .groupBy(orders.userId);

import { sql } from 'kysely'

// Total of all order amounts
const total = await db
  .selectFrom('orders')
  .select(sql<number>`sum(amount)`.as('total'))
  .executeTakeFirst()

// Total per user
const perUser = await db
  .selectFrom('orders')
  .select([
    'user_id',
    sql<number>`sum(amount)`.as('total'),
  ])
  .groupBy('user_id')
  .execute()

// Total of all order amounts
const result = await orderRepository
  .createQueryBuilder("order")
  .select("SUM(order.amount)", "total")
  .getRawOne();

// Total per user
const perUser = await orderRepository
  .createQueryBuilder("order")
  .select("order.userId", "userId")
  .addSelect("SUM(order.amount)", "total")
  .groupBy("order.userId")
  .getRawMany();

// Total of all order amounts
const total = await orm.em
  .createQueryBuilder(Order, "o")
  .select("sum(o.amount) as total")
  .execute("get");

// Total per user
const perUser = await orm.em
  .createQueryBuilder(Order, "o")
  .select(["o.user_id as user_id", "sum(o.amount) as total"])
  .groupBy("o.user_id")
  .execute();

import { col, fn } from "sequelize";

// Total of all order amounts
const total = await Order.sum("amount");

// Total per user
const perUser = await Order.findAll({
  attributes: [
    [col("user_id"), "userId"],
    [fn("SUM", col("amount")), "total"],
  ],
  group: ["user_id"],
});

SELECT AVG(amount) AS average FROM orders;

-- Average per user
SELECT user_id, AVG(amount) AS average
FROM orders
GROUP BY user_id;

from django.db.models import Avg

average = Order.objects.aggregate(avg=Avg("amount"))["avg"]

# Average per user
per_user = Order.objects.values("user_id").annotate(avg=Avg("amount"))

from sqlalchemy import select, func

with Session(engine) as session:
    average = session.scalar(
        select(func.avg(Order.amount))
    )

    # Average per user
    rows = session.execute(
        select(Order.user_id, func.avg(Order.amount).label("average"))
        .group_by(Order.user_id)
    ).all()

from sqlalchemy import func
from sqlmodel import select

with Session(engine) as session:
    average = session.exec(
        select(func.avg(Order.amount))
    ).one()

    # Average per user
    per_user = session.exec(
        select(Order.user_id, func.avg(Order.amount).label("avg"))
        .group_by(Order.user_id)
    ).all()

from tortoise.functions import Avg

avg = await Order.annotate(
    avg=Avg("amount")
).values("avg")

# Average per user
per_user = await Order.annotate(
    avg=Avg("amount")
).group_by("user_id").values("user_id", "avg")

from peewee import fn

average = Order.select(fn.AVG(Order.amount)).scalar()

# Average per user
per_user = (
    Order.select(
        Order.user,
        fn.AVG(Order.amount).alias("avg"),
    )
    .group_by(Order.user)
)

const result = await prisma.order.aggregate({
  _avg: { amount: true },
});
const average = result._avg.amount;

// Average per user
const perUser = await prisma.order.groupBy({
  by: ["userId"],
  _avg: { amount: true },
});

const result = await db.select({
  average: avg(orders.amount),
}).from(orders);

// Average per user
const perUser = await db.select({
  userId: orders.userId,
  average: avg(orders.amount),
})
  .from(orders)
  .groupBy(orders.userId);

import { sql } from 'kysely'

const average = await db
  .selectFrom('orders')
  .select(sql<number>`avg(amount)`.as('avg'))
  .executeTakeFirst()

// Average per user
const perUser = await db
  .selectFrom('orders')
  .select([
    'user_id',
    sql<number>`avg(amount)`.as('avg'),
  ])
  .groupBy('user_id')
  .execute()

const result = await orderRepository
  .createQueryBuilder("order")
  .select("AVG(order.amount)", "average")
  .getRawOne();

// Average per user
const perUser = await orderRepository
  .createQueryBuilder("order")
  .select("order.userId", "userId")
  .addSelect("AVG(order.amount)", "average")
  .groupBy("order.userId")
  .getRawMany();

const average = await orm.em
  .createQueryBuilder(Order, "o")
  .select("avg(o.amount) as avg")
  .execute("get");

// Average per user
const perUser = await orm.em
  .createQueryBuilder(Order, "o")
  .select(["o.user_id as user_id", "avg(o.amount) as avg"])
  .groupBy("o.user_id")
  .execute();

import { col, fn } from "sequelize";

const average = await Order.avg("amount");

// Average per user
const perUser = await Order.findAll({
  attributes: [
    [col("user_id"), "userId"],
    [fn("AVG", col("amount")), "avg"],
  ],
  group: ["user_id"],
});

SELECT MIN(amount) AS smallest, MAX(amount) AS largest
FROM orders;

-- Per user
SELECT user_id, MIN(amount), MAX(amount)
FROM orders
GROUP BY user_id;

from django.db.models import Min, Max

result = Order.objects.aggregate(
    smallest=Min("amount"),
    largest=Max("amount"),
)

# Per user
per_user = Order.objects.values("user_id").annotate(
    smallest=Min("amount"),
    largest=Max("amount"),
)

from sqlalchemy import select, func

with Session(engine) as session:
    row = session.execute(
        select(
            func.min(Order.amount).label("smallest"),
            func.max(Order.amount).label("largest"),
        )
    ).one()

    # Per user
    rows = session.execute(
        select(
            Order.user_id,
            func.min(Order.amount),
            func.max(Order.amount),
        ).group_by(Order.user_id)
    ).all()

from sqlalchemy import func
from sqlmodel import select

with Session(engine) as session:
    result = session.exec(
        select(
            func.min(Order.amount).label("smallest"),
            func.max(Order.amount).label("largest"),
        )
    ).one()

    # Per user
    per_user = session.exec(
        select(
            Order.user_id,
            func.min(Order.amount).label("smallest"),
            func.max(Order.amount).label("largest"),
        ).group_by(Order.user_id)
    ).all()

from tortoise.functions import Min, Max

result = await Order.annotate(
    smallest=Min("amount"),
    largest=Max("amount"),
).values("smallest", "largest")

# Per user
per_user = await Order.annotate(
    smallest=Min("amount"),
    largest=Max("amount"),
).group_by("user_id").values(
    "user_id", "smallest", "largest"
)

from peewee import fn

result = Order.select(
    fn.MIN(Order.amount).alias("smallest"),
    fn.MAX(Order.amount).alias("largest"),
).dicts().get()

# Per user
per_user = (
    Order.select(
        Order.user,
        fn.MIN(Order.amount).alias("smallest"),
        fn.MAX(Order.amount).alias("largest"),
    )
    .group_by(Order.user)
)

const result = await prisma.order.aggregate({
  _min: { amount: true },
  _max: { amount: true },
});

// Per user
const perUser = await prisma.order.groupBy({
  by: ["userId"],
  _min: { amount: true },
  _max: { amount: true },
});

const result = await db.select({
  smallest: min(orders.amount),
  largest: max(orders.amount),
}).from(orders);

// Per user
const perUser = await db.select({
  userId: orders.userId,
  smallest: min(orders.amount),
  largest: max(orders.amount),
})
  .from(orders)
  .groupBy(orders.userId);

import { sql } from 'kysely'

const result = await db
  .selectFrom('orders')
  .select([
    sql<number>`min(amount)`.as('smallest'),
    sql<number>`max(amount)`.as('largest'),
  ])
  .executeTakeFirst()

// Per user
const perUser = await db
  .selectFrom('orders')
  .select([
    'user_id',
    sql<number>`min(amount)`.as('smallest'),
    sql<number>`max(amount)`.as('largest'),
  ])
  .groupBy('user_id')
  .execute()

const result = await orderRepository
  .createQueryBuilder("order")
  .select("MIN(order.amount)", "smallest")
  .addSelect("MAX(order.amount)", "largest")
  .getRawOne();

// Per user
const perUser = await orderRepository
  .createQueryBuilder("order")
  .select("order.userId", "userId")
  .addSelect("MIN(order.amount)", "smallest")
  .addSelect("MAX(order.amount)", "largest")
  .groupBy("order.userId")
  .getRawMany();

const result = await orm.em
  .createQueryBuilder(Order, "o")
  .select([
    "min(o.amount) as smallest",
    "max(o.amount) as largest",
  ])
  .execute("get");

// Per user
const perUser = await orm.em
  .createQueryBuilder(Order, "o")
  .select([
    "o.user_id as user_id",
    "min(o.amount) as smallest",
    "max(o.amount) as largest",
  ])
  .groupBy("o.user_id")
  .execute();

import { col, fn } from "sequelize";

const result = {
  smallest: await Order.min("amount"),
  largest: await Order.max("amount"),
};

// Per user
const perUser = await Order.findAll({
  attributes: [
    [col("user_id"), "userId"],
    [fn("MIN", col("amount")), "smallest"],
    [fn("MAX", col("amount")), "largest"],
  ],
  group: ["user_id"],
});

-- Orders per month
SELECT
  DATE_TRUNC('month', created_at) AS month,
  COUNT(*) AS order_count,
  SUM(amount) AS total
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

from django.db.models import Count, Sum
from django.db.models.functions import TruncMonth

# Orders per month
per_month = (
    Order.objects
    .annotate(month=TruncMonth("created_at"))
    .values("month")
    .annotate(order_count=Count("id"), total=Sum("amount"))
    .order_by("month")
)

from sqlalchemy import select, func

with Session(engine) as session:
    month = func.date_trunc("month", Order.created_at).label("month")
    rows = session.execute(
        select(
            month,
            func.count().label("order_count"),
            func.sum(Order.amount).label("total"),
        )
        .group_by(month)
        .order_by(month)
    ).all()

from sqlalchemy import func
from sqlmodel import select

with Session(engine) as session:
    month = func.date_trunc("month", Order.created_at).label("month")
    per_month = session.exec(
        select(
            month,
            func.count(Order.id).label("order_count"),
            func.sum(Order.amount).label("total"),
        )
        .group_by(month)
        .order_by(month)
    ).all()

# No TruncMonth API; use raw SQL
from tortoise import connections

conn = connections.get("default")
rows = await conn.execute_query_dict(
    "SELECT DATE_TRUNC('month', created_at) "
    "  AS month, "
    "COUNT(*) AS order_count, "
    "SUM(amount) AS total "
    "FROM orders "
    "GROUP BY month ORDER BY month"
)

from peewee import fn

month = fn.DATE_TRUNC("month", Order.created_at).alias("month")

# Orders per month
per_month = (
    Order.select(
        month,
        fn.COUNT(Order.id).alias("order_count"),
        fn.SUM(Order.amount).alias("total"),
    )
    .group_by(month)
    .order_by(month)
)

// Prisma groupBy supports model fields only;
// for date truncation, use $queryRaw
const perMonth = await prisma.$queryRaw`
  SELECT
    DATE_TRUNC('month', "created_at") AS month,
    COUNT(*)::int AS order_count,
    SUM(amount) AS total
  FROM orders
  GROUP BY DATE_TRUNC('month', "created_at")
  ORDER BY month
`;

const month = sql`DATE_TRUNC('month', ${orders.createdAt})`;

const result = await db.select({
  month,
  orderCount: count(),
  total: sum(orders.amount),
})
  .from(orders)
  .groupBy(month)
  .orderBy(month);

import { sql } from 'kysely'

// Orders per month
const perMonth = await db
  .selectFrom('orders')
  .select([
    sql<Date>`date_trunc('month', created_at)`.as('month'),
    sql<number>`count(id)`.as('order_count'),
    sql<number>`sum(amount)`.as('total'),
  ])
  .groupBy(sql`date_trunc('month', created_at)`)
  .orderBy('month', 'asc')
  .execute()

const result = await orderRepository
  .createQueryBuilder("order")
  .select("DATE_TRUNC('month', order.created_at)", "month")
  .addSelect("COUNT(*)", "orderCount")
  .addSelect("SUM(order.amount)", "total")
  .groupBy("DATE_TRUNC('month', order.created_at)")
  .orderBy("month")
  .getRawMany();

// Orders per month
const perMonth = await orm.em
  .createQueryBuilder(Order, "o")
  .select([
    "date_trunc('month', o.created_at) as month",
    "count(o.id) as order_count",
    "sum(o.amount) as total",
  ])
  .groupBy("date_trunc('month', o.created_at)")
  .orderBy({ month: "asc" })
  .execute();

import { col, fn, literal } from "sequelize";

// Orders per month
const perMonth = await Order.findAll({
  attributes: [
    [fn("DATE_TRUNC", "month", col("created_at")), "month"],
    [fn("COUNT", col("id")), "orderCount"],
    [fn("SUM", col("amount")), "total"],
  ],
  group: [literal(`DATE_TRUNC('month', "created_at")`)],
  order: [[literal(`DATE_TRUNC('month', "created_at")`), "ASC"]],
});

-- Users who spent more than 1000 total
SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 1000;

from django.db.models import Sum

users = (
    Order.objects
    .values("user_id")
    .annotate(total=Sum("amount"))
    .filter(total__gt=1000)
)

from sqlalchemy import select, func

with Session(engine) as session:
    rows = session.execute(
        select(Order.user_id, func.sum(Order.amount).label("total"))
        .group_by(Order.user_id)
        .having(func.sum(Order.amount) > 1000)
    ).all()

from sqlalchemy import func
from sqlmodel import select

with Session(engine) as session:
    users = session.exec(
        select(Order.user_id, func.sum(Order.amount).label("total"))
        .group_by(Order.user_id)
        .having(func.sum(Order.amount) > 1000)
    ).all()

from tortoise.functions import Sum

results = await Order.annotate(
    total=Sum("amount")
).group_by("user_id").filter(
    total__gt=1000
).values("user_id", "total")

from peewee import fn

users = (
    Order.select(
        Order.user,
        fn.SUM(Order.amount).alias("total"),
    )
    .group_by(Order.user)
    .having(fn.SUM(Order.amount) > 1000)
)

const result = await prisma.order.groupBy({
  by: ["userId"],
  _sum: { amount: true },
  having: {
    amount: { _sum: { gt: 1000 } },
  },
});

const result = await db.select({
  userId: orders.userId,
  total: sum(orders.amount),
})
  .from(orders)
  .groupBy(orders.userId)
  .having(gt(sum(orders.amount), 1000));

import { sql } from 'kysely'

const users = await db
  .selectFrom('orders')
  .select([
    'user_id',
    sql<number>`sum(amount)`.as('total'),
  ])
  .groupBy('user_id')
  .having(sql`sum(amount)`, '>', 1000)
  .execute()

const result = await orderRepository
  .createQueryBuilder("order")
  .select("order.userId", "userId")
  .addSelect("SUM(order.amount)", "total")
  .groupBy("order.userId")
  .having("SUM(order.amount) > :min", { min: 1000 })
  .getRawMany();

const users = await orm.em
  .createQueryBuilder(Order, "o")
  .select(["o.user_id as user_id", "sum(o.amount) as total"])
  .groupBy("o.user_id")
  .having("sum(o.amount) > ?", [1000])
  .execute();

import { Op, col, fn, where } from "sequelize";

const users = await Order.findAll({
  attributes: [
    [col("user_id"), "userId"],
    [fn("SUM", col("amount")), "total"],
  ],
  group: ["user_id"],
  having: where(fn("SUM", col("amount")), Op.gt, 1000),
});

-- Users whose orders exceed the average amount
SELECT * FROM users
WHERE id IN (
  SELECT user_id FROM orders
  WHERE amount > (SELECT AVG(amount) FROM orders)
);

from django.db.models import Avg, Subquery

avg_sub = Order.objects.values(
    avg=Avg("amount")
)

users = User.objects.filter(
    id__in=Subquery(
        Order.objects.filter(
            amount__gt=Subquery(avg_sub[:1])
        ).values("user_id")
    )
)

from sqlalchemy import select, func

with Session(engine) as session:
    avg_amount = select(func.avg(Order.amount)).scalar_subquery()

    above_avg_users = select(Order.user_id).where(
        Order.amount > avg_amount
    ).distinct()

    users = session.scalars(
        select(User).where(User.id.in_(above_avg_users))
    ).all()

from sqlalchemy import func
from sqlmodel import select

with Session(engine) as session:
    avg_subquery = select(func.avg(Order.amount)).scalar_subquery()

    users = session.exec(
        select(User).where(
            User.id.in_(
                select(Order.user_id)
                .where(Order.amount > avg_subquery)
            )
        )
    ).all()

# No ORM subquery API; using raw SQL
from tortoise import connections

conn = connections.get("default")
rows = await conn.execute_query_dict(
    "SELECT * FROM users "
    "WHERE id IN ("
    "  SELECT user_id FROM orders "
    "  WHERE amount > ("
    "    SELECT AVG(amount) FROM orders"
    "  )"
    ")"
)

from peewee import fn

avg_subquery = Order.select(fn.AVG(Order.amount))

users = User.select().where(
    User.id.in_(
        Order.select(Order.user).where(
            Order.amount > avg_subquery
        )
    )
)

// Prisma doesn't support subqueries natively;
// split into two queries
const avg = await prisma.order.aggregate({
  _avg: { amount: true },
});

const users = await prisma.user.findMany({
  where: {
    orders: {
      some: { amount: { gt: avg._avg.amount! } },
    },
  },
});

const avgAmount = db.select({
  value: avg(orders.amount),
}).from(orders);

const aboveAvgUsers = db.selectDistinct({
  userId: orders.userId,
})
  .from(orders)
  .where(gt(orders.amount, avgAmount));

const result = await db.select()
  .from(users)
  .where(inArray(users.id, aboveAvgUsers));

import { sql } from 'kysely'

const users = await db
  .selectFrom('users')
  .selectAll()
  .where('id', 'in',
    db
      .selectFrom('orders')
      .select('user_id')
      .where('amount', '>',
        db
          .selectFrom('orders')
          .select(sql<number>`avg(amount)`)
      )
  )
  .execute()

const result = await userRepository
  .createQueryBuilder("user")
  .where((qb) => {
    const subQuery = qb.subQuery()
      .select("order.userId")
      .from(Order, "order")
      .where("order.amount > (SELECT AVG(amount) FROM orders)")
      .getQuery();
    return "user.id IN " + subQuery;
  })
  .getMany();

const users = await orm.em
  .createQueryBuilder(User, "u")
  .select("u.*")
  .where(`u.id in (
    select o.user_id
    from orders o
    where o.amount > (
      select avg(amount) from orders
    )
  )`)
  .getResultList();

import { Op, literal } from "sequelize";

const users = await User.findAll({
  where: {
    id: {
      [Op.in]: literal(`(
        SELECT user_id FROM orders
        WHERE amount > (
          SELECT AVG(amount) FROM orders
        )
      )`),
    },
  },
});