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)