Skip to main content

Aggregation

Aggregation​

The Aggregate option allows adding one or more aggregation functions.

package main

import (
"context"

"<project>/ent"
"<project>/ent/payment"
"<project>/ent/pet"
)

func Do(ctx context.Context, client *ent.Client) {
// Aggregate one field.
sum, err := client.Payment.Query().
Aggregate(
ent.Sum(payment.Amount),
).
Int(ctx)

// Aggregate multiple fields.
var v []struct {
Sum, Min, Max, Count int
}
err := client.Pet.Query().
Aggregate(
ent.Sum(pet.FieldAge),
ent.Min(pet.FieldAge),
ent.Max(pet.FieldAge),
ent.Count(),
).
Scan(ctx, &v)
}

Group By​

Group by name and age fields of all users, and sum their total age.

package main

import (
"context"

"<project>/ent"
"<project>/ent/user"
)

func Do(ctx context.Context, client *ent.Client) {
var v []struct {
Name string `json:"name"`
Age int `json:"age"`
Sum int `json:"sum"`
Count int `json:"count"`
}
err := client.User.Query().
GroupBy(user.FieldName, user.FieldAge).
Aggregate(ent.Count(), ent.Sum(user.FieldAge)).
Scan(ctx, &v)
}

Group by one field.

package main

import (
"context"

"<project>/ent"
"<project>/ent/user"
)

func Do(ctx context.Context, client *ent.Client) {
names, err := client.User.
Query().
GroupBy(user.FieldName).
Strings(ctx)
}

Group By Edge​

Custom aggregation functions can be useful if you want to write your own storage-specific logic.

The following shows how to group by the id and the name of all users and calculate the average age of their pets.

package main

import (
"context"
"log"

"<project>/ent"
"<project>/ent/pet"
"<project>/ent/user"
)

func Do(ctx context.Context, client *ent.Client) {
var users []struct {
ID int
Name string
Average float64
}
err := client.User.Query().
GroupBy(user.FieldID, user.FieldName).
Aggregate(func(s *sql.Selector) string {
t := sql.Table(pet.Table)
s.Join(t).On(s.C(user.FieldID), t.C(pet.OwnerColumn))
return sql.As(sql.Avg(t.C(pet.FieldAge)), "average")
}).
Scan(ctx, &users)
}

Having + Group By​

Custom SQL modifiers can be useful if you want to control all query parts. The following shows how to retrieve the oldest users for each role.

package main

import (
"context"
"log"

"entgo.io/ent/dialect/sql"
"<project>/ent"
"<project>/ent/user"
)

func Do(ctx context.Context, client *ent.Client) {
var users []struct {
Id Int
Age Int
Role string
}
err := client.User.Query().
Modify(func(s *sql.Selector) {
s.GroupBy(user.Role)
s.Having(
sql.EQ(
user.FieldAge,
sql.Raw(sql.Max(user.FieldAge)),
),
)
}).
ScanX(ctx, &users)
}

Note: The sql.Raw is crucial to have. It tells the predicate that sql.Max is not an argument.

The above code essentially generates the following SQL query:

SELECT * FROM user GROUP BY user.role HAVING user.age = MAX(user.age)